Re: minimal wal_level on subscriber
Le 16/01/2020 à 19:58, Laurenz Albe a écrit : On Thu, 2020-01-16 at 12:09 +0100, Arnaud L. wrote: is it OK to set wal_level to minimal on the subscriber side of the logical replication ? Yes, if you don't want physical backups. You mean online physical backups ? I could still shutdown the subscriber and take an offline physical backup, right ? Regards -- Arnaud
minimal wal_level on subscriber
Hi list, is it OK to set wal_level to minimal on the subscriber side of the logical replication ? Thanks ! Regards -- Arnaud
Re: psql \copy hanging
Le 08/10/2019 à 16:59, Arnaud L. a écrit : Yes, I already did that, and unfortunately there's no activy. There is absolutely no activity in procmon using psql.exe as a filter. process-xp is not giving more information, processor usage is 0%. My apologies, I obviously did something wrong last time I checked this process with process-xp and procmon. Now I see that there IS activity on the problematic process ! 100% CPU and some (but not much) disk activity. I think I'll try to let it run for some time to get the auto_explain do its work. If I kill the backend now, I won't see anything I believe. I dont now it it'll ever complete this query though, it usually takes ~100 seconds, and here it has already been running for 9 hours. Regards -- Arnaud
Re: psql \copy hanging
Le 08/10/2019 à 16:55, Daniel Verite a écrit : Arnaud L. wrote: Anyway, it hung using this syntax during last night's run. I'll give it another try tonight just to be sure. When psql.exe is hanging, maybe you could use a tool like Process Monitor [1] or Process Explorer [2] to get insights about what it's stuck on or what it's doing exactly. [1] https://docs.microsoft.com/en-us/sysinternals/downloads/procmon [2] https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer Yes, I already did that, and unfortunately there's no activy. There is absolutely no activity in procmon using psql.exe as a filter. process-xp is not giving more information, processor usage is 0%. Regards -- Arnaud
Re: psql \copy hanging
Le 08/10/2019 à 16:03, Adrian Klaver a écrit : This is going to be hard to troubleshoot if you change your commands. Previously you had: psql -h %MYPGSERVER% -a -f myscript.sql %MYPGDB% Changing more then one thing at a time makes it that much more difficult to isolate the issue. Yes, true. Actually I do one change at a time, I'm just no posting every single attempt. So I tried to feed the script using console redirection rather than -f as you can see. That was the only change. I would create an entirely separate batch file that runs just problematicline.sql. OK, that's easy. Actually the batch file is not doing much more than running this psql command, but that's really not a problem/ Given that you suspect Postgres it would be helpful to see the query that underlies the view you are copying. You might want to look at autoexplain: https://www.postgresql.org/docs/11/auto-explain.html as a way of getting information at run time. OK that's nice. Since I don't want to mess with the whole server configuration, I added some auto_explain settings to my script. So for tonight, my script looks like this : LOAD 'auto_explain'; SET auto_explain.log_min_duration = 0; SET auto_explain.log_analyze = true; COPY (SELECT * FROM myview) TO STDOUT \g 'myserver\\myshare\\myfile.txt' And it'll run in a separate batch. Thanks for your help Adrian ! Regards -- Arnaud
Re: psql \copy hanging
Le 08/10/2019 à 12:55, Daniel Verite a écrit : Testing this with 11.5, it works for me. Make sure you're running the latest minor release (on the client side in this case), because a related fix was issued last February. For the 11 branch it was in version 11.2. OK, my bad, backslashes in a windows-style share path have to be escaped. Anyway, it hung using this syntax during last night's run. I'll give it another try tonight just to be sure. Regards -- Arnaud
Re: psql \copy hanging
Le 08/10/2019 à 12:55, Daniel Verite a écrit : Arnaud L. wrote: As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a script file (i.e. it does not work if the command is passed in a file via the -f argument). The command runs fine, no error is raised either by the client or the server, but no file is written. Testing this with 11.5, it works for me. Make sure you're running the latest minor release (on the client side in this case), because a related fix was issued last February. For the 11 branch it was in version 11.2. I'm on 11.5-1 on the client side, but I had added an output redirection for this batch file to try to understand what was happening during the night runs, and that might be the reason why \g fails (i.e. some interference between the console redirections). I'll give it another try without these redirections. Regards -- Arnaud
Re: psql \copy hanging
Le 08/10/2019 à 09:28, Pavel Stehule a écrit : you can write simple C application with COPY API https://www.postgresql.org/docs/12/libpq-copy.html Unfortunately, I don't know C. Then you can eliminate or ensure locality of problem. more, you can use server side copy. Superuser can read data from server file system. Yes, but in this case the file has to be written to a network share, and the windows user under wich PostgreSQL runs (Network Service) cannot be given write permission on this share. That's the reason for the use of \copy. Now that I think about it, *maybe* this started happening after a server upgrade. Since this is intermittent, I'm not really sure about this, but some time ago we moved our server to a different hardware and upgraded from 9.3 to 11 at the same time. The dates don't perfectly match though, we upgraded around 8th of august and the problem arose ~2 weeks later for the first time. The client was upgraded around that same time period (not exactly the same time if I remember correctly). Regards -- Arnaud
Re: psql \copy hanging
Le 07/10/2019 à 16:36, Adrian Klaver a écrit : So you are saying that you have not run the problematic line by itself? It hung during last night's run. I had modified my batch script to run the \copy commands separately, i.e. it now reads as : psql -h myserver -a mydb < originalscriptwithoutproblematicline.sql psql -h myserver -a mydb < problematicline.sql It hung at the problematic line, so during the second psql command. I'm really at loss... I *believe* that the problem lies either in psql or in PostgreSQL, but I really don't know what to try now. Regards -- Arnaud
Re: psql \copy hanging
Le 07/10/2019 à 16:36, Adrian Klaver a écrit : So you are saying that you have not run the problematic line by itself? It depends what you mean by that. I've run this line by itself many times. Everytime the script has failed in fact. But until today I had not splitted the batch script to call two separate SQL scripts with one containing only the problematic line, no. I've changed it this morning, so we'll see how it goes now. Regards -- Arnaud
Re: psql \copy hanging
Le 04/10/2019 à 19:08, Adrian Klaver a écrit : On 10/4/19 12:19 AM, Arnaud L. wrote: OK I can do that. I thought I nailed it down to this line because it started failing when this line was ~5th in the script, and it kept failing on that very same line after I moved it at the very end of the script (that's where it is now). Which tends to point to it as the problem. The question is whether it exhibits that behavior on its own or only when in combination with the other commands. Yes. It ran fine this last night. I had moved the line back to its original place, so now everything is exactly like it was before it started showing this behaviour. So, still apparently random... As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a script file (i.e. it does not work if the command is passed in a file via the -f argument). The command runs fine, no error is raised either by the client or the server, but no file is written. Yeah not sure how that is supposed to work: [...] production_(postgres)# \copy (select * from cell_per) TO STDOUT \g 'cell.txt' ERROR: syntax error at or near "\" LINE 1: COPY ( select * from cell_per ) TO STDOUT \g 'cell.txt' This works with real SQL commands, so it should be "COPY" here, not "\copy". Regards -- Arnaud
Re: psql \copy hanging
Le 03/10/2019 à 16:32, Adrian Klaver a écrit : I may have missed it before, but where is the Postgres server located? On the same local area network. Not on the computer running the script (so direct COPY TO is not an option). Given that this seems to be some sort of resource issue and that the below contains commands that are not dependent on each other, have you thought of splitting myscript.sql into two scripts? If nothing else the n(total line)-problem line in one script and the problem line in another. That would help nail down whether that line is truly the problem or if it is an interaction with running the other 50+ lines. OK I can do that. I thought I nailed it down to this line because it started failing when this line was ~5th in the script, and it kept failing on that very same line after I moved it at the very end of the script (that's where it is now). As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a script file (i.e. it does not work if the command is passed in a file via the -f argument). The command runs fine, no error is raised either by the client or the server, but no file is written. Regards -- Arnaud
Re: psql \copy hanging
Le 03/10/2019 à 15:54, Adrian Klaver a écrit : On 10/2/19 11:51 PM, Arnaud L. wrote: Well, this problem is still bugging me, and this time I've tried with a local file. Unfortunately, it did not help. To further rule out filesystem problems, I first took care to delete the target files before copying to it, but it did not help either. So now I'm quite confident that the problem is either psql or even postgresql itself. Does anyone know of anything I could try to try to fix or debug this ? Going back to the original thread I noticed it was not specified what program was being used to run the script in the overnight session. So what is being used to run the script overnight? Yes, sorry for having lost the original thread, my mailbox has a quite stupid automatic purge schedule... The script is run in a windows batch file. Basically, export.bat contains : SET PGUSER=myuser SET PGPASSWORD=mypwd SET PGCLIENTENCODING=UTF8 SET MYPGSERVER=myserverurl SET MYPGDB=mydatabase psql -h %MYPGSERVER% -a -f myscript.sql %MYPGDB% And myscript.sql contains : \copy (SELECT * FROM view1) TO '\\server\share\view1.txt' \copy (SELECT * FROM view2) TO '\\server\share\view2.txt' ... etc with ~60 views Today, I've update the problematic \copy line to be : COPY (SELECT * FROM view) TO STDOUT \g '\\server\share\view.txt' I'll keep you informed (even though a successfull run is not a guarantee of success, because the original script did sometimes work). Regards -- Arnaud
Bad estimates on GIN bigint[] index
Le 03/09/2019 à 15:43, Tom Lane a écrit :
"Arnaud L." writes:
-> Bitmap Index Scan on planet_osm_ways_nodes_idx
(cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.268..0.268
rows=1 loops=1)
Index Cond: (nodes && '{1}'::bigint[])
The planner should be able to do better than that, given up-to-date
statistics on the "nodes" column.
Sorry to up this thread, but is there anything I can do to help the
planner in this particular case ?
REINDEXing did not help, nor did ANALYZEing with different STATISTICS
target for this specific column.
Regards
--
Arnaud
Re: Slow statement using parallelism after 9.6>11 upgrade
Le 04/09/2019 à 09:04, Arnaud L. a écrit : Le 03/09/2019 à 15:43, Tom Lane a écrit : The planner should be able to do better than that, given up-to-date statistics on the "nodes" column. Tom, I can confirm that with up to date statistics the planner is still lost. I did a REINDEX to rule out a broken index and the estimate is still in the 100k+ range. Setting STATISTICS target to 10.000 on the nodes column and re-analyzing did not help either. Estimates are still ~1.4M rows when result is 1 row. Regards -- Arnaud
Re: Slow statement using parallelism after 9.6>11 upgrade
Le 04/09/2019 à 09:04, Arnaud L. a écrit :
Tom, I can confirm that with up to date statistics the planner is still
lost.
I did a REINDEX to rule out a broken index and the estimate is still in
the 100k+ range.
Sorry, I meant 1M+ range.
EXPLAIN ANALYZE select id from planet_osm_ways WHERE nodes &&
ARRAY[123456789::bigint];
(parallel_workers = 0 on the table as per Paul's recommandation) :
Bitmap Heap Scan on planet_osm_ways (cost=11582.45..3535447.30
rows=1419000 width=8) (actual time=0.198..0.199 rows=1 loops=1)
Recheck Cond: (nodes && '{123456789}'::bigint[])
Heap Blocks: exact=1
-> Bitmap Index Scan on planet_osm_ways_nodes_idx
(cost=0.00..11227.70 rows=1419000 width=0) (actual time=0.151..0.151
rows=1 loops=1)
Index Cond: (nodes && '{123456789}'::bigint[])
Planning Time: 0.260 ms
Execution Time: 0.249 ms
Regards
--
Arnaud
Re: Slow statement using parallelism after 9.6>11 upgrade
Le 03/09/2019 à 15:43, Tom Lane a écrit :
"Arnaud L." writes:
We have upgraded our database from 9.6 to 11 (and updated PostGIS from
2.3 to 2.5 as well).
...
Have you re-ANALYZEd the database? The problem with this query
seems to be the spectacularly awful rowcount estimate here:
-> Bitmap Index Scan on planet_osm_ways_nodes_idx
(cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.268..0.268
rows=1 loops=1)
Index Cond: (nodes && '{1}'::bigint[])
The planner should be able to do better than that, given up-to-date
statistics on the "nodes" column.
Tom, I can confirm that with up to date statistics the planner is still
lost.
I did a REINDEX to rule out a broken index and the estimate is still in
the 100k+ range.
Regards
--
Arnaud
Re: Slow statement using parallelism after 9.6>11 upgrade
Le 03/09/2019 à 17:02, Imre Samu a écrit : > We have upgraded our database from 9.6 to 11 This is now the latest PG ? PG11.5? Yes, PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit. - the osm2pgsql has an own parallelizations ... ( osm2pgsql --number-processes .. ) so be careful to add more parallelisation to the PG side with the same osm2pgsql parameters ! ( check the memory usages! ) - check the benchmarks and the tunnings: https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks - you can ask help on the : "osm dev mail list" (="dev OpenStreetMap developer discusssion" https://lists.openstreetmap.org/listinfo/dev ) Yes, I know. It as been fine tuned over some time now to fit our server's capacity. Disabling parallelisme has allowed the update to run in ~5000s, which is consistent with what we could witness before the upgrade. I had started writing an issue on the osm2pgsql github before realizing it was probably a PG-only issue. Cheers -- Arnaud
Re: Slow statement using parallelism after 9.6>11 upgrade
Le 03/09/2019 à 16:39, Paul Ramsey a écrit : On Sep 3, 2019, at 7:06 AM, Arnaud L. wrote: I've setup parallel_tuple_cost to 1.0 parallel_setup_cost to 5000.0 for the time being which solves this specific problem. These value don't look very sensible though, they are very high compared to the default ones. You can also leave that setting unchanged and just change the behaviour on your one table: ALTER TABLE nodes SET ( parallel_workers = 0); Nice, this will fix this precise query and leave parallelism in place for the other ones. Thanks ! -- Arnaud
Re: Slow statement using parallelism after 9.6>11 upgrade
Le 03/09/2019 à 15:43, Tom Lane a écrit : "Arnaud L." writes: We have upgraded our database from 9.6 to 11 (and updated PostGIS from 2.3 to 2.5 as well). ... Have you re-ANALYZEd the database? The problem with this query seems to be the spectacularly awful rowcount estimate here: You mean after the upgrade process ? Yes I have. I've juste re-run "ANALYZE table" to rule this out, estimates are the same. Maybe some statistic target problem ? Estimated number of rows is 284.196.352 Also, this is a GIN index on a bigint[] column. I've setup parallel_tuple_cost to 1.0 parallel_setup_cost to 5000.0 for the time being which solves this specific problem. These value don't look very sensible though, they are very high compared to the default ones. Cheers -- Arnaud
Slow statement using parallelism after 9.6>11 upgrade
Hi list
We have upgraded our database from 9.6 to 11 (and updated PostGIS from
2.3 to 2.5 as well).
We are using it among other things to store an OpenStreetMap database,
populated by the osm2pgsql program and updated on a daily basis.
The process used to take ~1h30 minutes before the upgrade, but after the
upgrade it looks like it will never run to completion in a 24h time frame.
The problem is apparently that the planner choses to run some statements
in parallel, but these take a lot longer than when run without it.
Here are the to explain analyze of the same statement, with parallelism
on and off :
Statement :
PREPARE mark_ways_by_node(int8) AS select id from planet_osm_ways WHERE
nodes && ARRAY[$1];
EXPLAIN EXECUTE mark_ways_by_node(1);
max_parallel_workers_per_gather = 2 :
Gather (cost=12545.61..2357352.25 rows=1420982 width=8) (actual
time=2.577..64.028 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Bitmap Heap Scan on planet_osm_ways
(cost=11545.61..2214254.05 rows=592076 width=8) (actual
time=0.213..0.213 rows=0 loops=3)
Recheck Cond: (nodes && '{1}'::bigint[])
Heap Blocks: exact=1
-> Bitmap Index Scan on planet_osm_ways_nodes_idx
(cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.173..0.174
rows=1 loops=1)
Index Cond: (nodes && '{1}'::bigint[])
Planning Time: 8.596 ms
Execution Time: 64.135 ms
max_parallel_workers_per_gather = 0 :
Bitmap Heap Scan on planet_osm_ways (cost=11545.61..3462154.46
rows=1420982 width=8) (actual time=0.677..0.679 rows=1 loops=1)
Recheck Cond: (nodes && '{1}'::bigint[])
Heap Blocks: exact=1
-> Bitmap Index Scan on planet_osm_ways_nodes_idx
(cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.268..0.268
rows=1 loops=1)
Index Cond: (nodes && '{1}'::bigint[])
Planning Time: 0.193 ms
Execution Time: 0.773 ms
So this Parallel Bitmap Heap Scan seems to be quite problematic here.
What runtime setting should I change to fix this, without losing the
benefit of parallelism ?
Thanks a lot !
Cheers
--
Arnaud
Re: psql \copy hanging
Le 28/08/2019 à 09:43, Luca Ferrari a écrit : I don't want to be pedantic, but I would have tried with a single change at a time. And my bet is: the local file would do the trick (i.e., it is a weird share problem). You're not don't worry. This process is quite important in our workflow (not critical), so I can't really afford to make tests for weeks. Moving the offending line to the end of the script is what I'd consider "single change". To me it makes something clear : the problem occurs only at the very specific time this command is running. If you are going to invest some time, you could also try to write a small file on the share just before the copy starts, so that you are guaranteed the share is working. Something like: echo $(date) >> $SHARE/log.txt psql 'copy ...' echo 'done' >> $SHARE/log.txt Well, I do know that the share is working just before this command runs because ALL the commands in the script write to this specific share. The script is basically outputting many views results in a single share, something like : \copy (select * from view1) TO '\\myserver\myshare\file1.csv' \copy (select * from view2) TO '\\myserver\myshare\file2.csv' ... \copy (select * from view99) TO '\\myserver\myshare\file99.csv' So the \copy command right before (say view15 for instance) is writing to this same share just milliseconds before the the problematic command (view16) tries to do the same. Since this particular view takes some time to execute, there is some time between the moment the file gets created on the share (which happens immediately when the \copy command runs if I get it right), and the moment psql receives content and starts writing to it (my tests suggest 1min to 1min30s). Either psql doesn't receive anything (possible, since the connection is marked as active but it does not look as if it's doing anything at all), or there has been some timeout. It could have been tcp keepalive, but in Windows the default is 2h I believe and postgresql uses system default if nothing is specified in conf (which is my case). So with all this in mind I'd rather think I have a problem with either psql's \copy or with my query on the server side. But I'm not rulling anything out of course. One other thing I could try is using COPY TO STDOUT \g. From what I understand in the documentation this would not be 100% similar to what \copy is doing. Anyway, if it works with the current setup I won't dig into it much deeper. Cheers -- Arnaud
Re: psql \copy hanging
Le 27/08/2019 à 13:17, Arnaud L. a écrit : I move the offending line at the end of the script, so it will run some minutes later, maybe this will be enough. OK, so this was enough for last night's schedule to run without problem. I still don't get it so I'm not satisfied with this solution, but at least it works. I'll keep the list informed if something new arises. Thanks to everyone for helping ! Cheers -- Arnaud
Re: psql \copy hanging
Le 27/08/2019 à 13:13, Luca Ferrari a écrit : My personal experience with shares and network that are not so much stable is that they can block, and that is why I'm suggesting to try on the local filesystem to see if that is a share-related problem or a resource problem. Also scheduling the script at a different time could help, because it could be at that time the machine (and it could mean the sharing machine) is busy at the point it does not respond. I'm quite confident that changing the schedule would help (since the script runs fine during work hours), but the time-table is already quite busy. I move the offending line at the end of the script, so it will run some minutes later, maybe this will be enough. I'll also try to write it directly on a local folder to rule this out. Cheers -- Arnaud
Re: psql \copy hanging
Le 27/08/2019 à 10:57, Luca Ferrari a écrit : On Tue, Aug 27, 2019 at 10:48 AM Arnaud L. wrote: I can run the script just fine during working hours. I meant thru your scheduler (cron or something). Yes, it runs fine too. There is indeed something happening on the server at this specific time but I don't know what. It hangs against the same line in the sql script, all lines being "\copy (select ) to 'file on unc share'". This is a new detail to me: what if you output to a local file and move it after on the share? I could do this but it would make the script a lot more complicated. There are a lot of views that I \copy directly to this share, and this is the only one that poses any problem. I still don't get: is the line content the longest or the highest numbered in the output? Sorry, I don't undertand either. Your question was "Does it hangs against the same line content or the same line number?". I run an sql script that contains a bunch of \copy commands. It is always the same \copy that is hanging. Also when the script is hung, output has not started (file size is 0). This makes me think there is some problem with the share, not the script. Again: test against a local file that you move (rsync?) to the share after. Everything is windows based, so rsync is not really an option. There are alternatives of course. I could write the file locally, but for robustness I'm trying not to rely on the local FS so that this script could be run from any machine on the network without modifications. Cheers -- Arnaud
Re: psql \copy hanging
Le 27/08/2019 à 10:00, Luca Ferrari a écrit : On Tue, Aug 27, 2019 at 9:54 AM Arnaud L. wrote: Any other idea ? I'll change the lines order for tonight's run, but that is not what I'd call a solution... Does it hangs against the same line content or the same line number? Are you able to run the script automatically during working hours (to avoid firewalling or upgrades running in parrallel to your nightly script execution)? Any chance something is querying the same data and a lock blocks the transaction (pg_locks)? Hi Luca. I can run the script just fine during working hours. I checked pg_locks, and this pid is the only process requesting locks. It has around 100 of them since it is a view querying multiple tables, but I see nothing blocking. All locks are granted. I have a csv output of pg_locks so I can post this if asked. It hangs against the same line in the sql script, all lines being "\copy (select ) to 'file on unc share'". This line is simply the longest running query because the view inside the select outputs almost 1M rows and does some subqueries. Also when the script is hung, output has not started (file size is 0). Just throwing on the table some desperate ideas Desperate ideas are very welcome ! Cheers -- Arnaud
Re: psql \copy hanging
Le 22/08/2019 à 10:47, Arnaud L. a écrit : On Windows, I have an unattended script that exports data overnight by issuing psql commands like : psql -f mycommands.sql whith mycommands.sql containing a bunch of \copy lines like : \copy (SELECT * FROM someview) TO '\\windowsserver\windowsshare\somefile.csv' When I run this script by hand, everything is fine but when it runs overnight it hangs and allways on the same line. There is no lock in the database, and the connection is marked as active in the backend and running the statement "COPY (SELECT * FROM myview) TO STDOUT". So where can things be hanging ? My first guess (appart from db lock) was that the target file was locked by the filesystem. But wouldn't psql know about it and fail with an error ? OK, so it happended again... I'm really puzzled. There is no lock on the FS that I can find (openfiles reports 0 lock on this file). The client psql process is sitting idle, doing nothing. In PostgreSQL, the connection is active with the query being simple copy (select * from view) to stdout. In pg_stat_activity wait_event_type IS NULL, state is active, query_start is 2019-08-27 00:18:20.471957+02 and state_change is 2019-08-27 00:18:20.47196+02... Nothing in postgresql LOG around that time. Later I have : 2019-08-27 01:00:04 CEST LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. But I realy doubt this is related... Any other idea ? I'll change the lines order for tonight's run, but that is not what I'd call a solution... Thanks a lot ! -- Arnaud
Re: psql \copy hanging
Le 22/08/2019 à 16:23, Arnaud L. a écrit : Le 22/08/2019 à 16:21, Adrian Klaver a écrit : The only thing I can think of at the moment is to put the offending line in a separate script and see what happens. Then I think I'll first try to switch the line orders. I'll execute this action last and see if it changes anything. If it doesn't, I'll break the script into smaller parts. It ran fine over the week-end so I'll consider this as temporary glitch. It's always dissatisfying not to know what has happened, but I'm not going to try too hard to reproduce this. Thanks for helping and sorry for the noise ! Cheers -- Arnaud
Re: psql \copy hanging
Le 22/08/2019 à 20:00, Jerry Sievers a écrit : Is the overnight run being done as the same DB user you are testing with? If not, then perhaps the automated user deviates in resource settings and/or permissions, in turn resulting in sluggish performance caused by thrashing and/or inferior query plan. Hi Jerry, yes, they both run under the same user. I've checked my other scheduled tasks and I don't see what could interfere, but then this is quite complex so I could easily overlook something. Unfortunately, tonight's run failed for a totally different reason... Thanks for your help Cheers -- Arnaud
Re: psql \copy hanging
Le 22/08/2019 à 16:21, Adrian Klaver a écrit : The only thing I can think of at the moment is to put the offending line in a separate script and see what happens. Then I think I'll first try to switch the line orders. I'll execute this action last and see if it changes anything. If it doesn't, I'll break the script into smaller parts. Thanks for you help ! -- Arnaud
Re: psql \copy hanging
Le 22/08/2019 à 16:00, Adrian Klaver a écrit : The first thing I think of when seeing this sort of behavior on Windows is anti-virus software. Doing you have any running on the machine? You say it always hangs on the same line. Is that line doing something different from the others? Say writing to a different location. Thanks for your insights Adrian. There is no AV software on the machine as it is a Server version of Windows. (I know some people run AV on Windows Server, but I don't) All the lines write to the same location which is a network share, but the hanging one is clearly the longest query to execute and the output file is one of the biggest one (though not the biggest). Note that the share points to a local folder. The fact the psql writes to a share is only for robustness so that this script could be executed from any machine. Cheers -- Arnaud
Re: Upgrading PostgreSQL under Windows
Le 27/03/2019 à 07:02, Kumar Prince NCS a écrit : We are Upgrading PostgreSQL under Windows from v9.3 to 9.5. Following steps from https://www.postgresql.org/docs/10/pgupgrade.html We are using PostgreSQL database for confluence application. While running Step 9 , there is command to set path with postgres user. As per installation of postgres database , we haven’t created “postgres” user on windows. When we ran pg_upgrade.exe we are getting below error You need to specify the user used for migration with the appropriate command line option, for instance "-U postgres". Othewise pg_upgrade will try to connect with your OS username, which obviously won't work. Since you can't specify a password in pg_upgrade's command, you will also need to provide authentication informations in a pgpass.conf file as per https://www.postgresql.org/docs/9.5/libpq-pgpass.html Regards -- Arnaud
Re: How to compare dates from two tables with blanks values
Le 19/12/2018 à 11:41, Mike Martin a écrit : cast(nullif(d.joineddate,NULL) as timestamp) != cast(nullif(s.joineddate,'') as timestamp) Try with d.joineddate IS DISTINCT FROM s.joineddate https://www.postgresql.org/docs/current/functions-comparison.html Cheers -- Arnaud
Re: BDR and PostgreSQL 12 and Windows support
Le 16/11/2018 à 15:28, Ravi Krishna a écrit : A better question should be, are there any production users of PG on Windows :-) And the answer is yes, there are some ! Cheers -- Arnaud
Re: Schema/Data conversion opensource tools from MySQL to PostgreSQL
Le 25-06-18 à 18:47, chiru r a écrit : Please suggest Schema/Data conversion opensource tools from MySQL to PostgreSQL. Hi. I used this php script which did a pretty good job : https://github.com/AnatolyUss/FromMySqlToPostgreSql -- Regards
Re: VBA to connect to postgresql from MS Access
Le 01-06-18 à 14:21, Łukasz Jarych a écrit :
ODBC;DSN=PostgreSQL35W;DATABASE=AccessTest;SERVER=localhost;PORT=5432;*CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4*
When i was searching code for this in internet i found only:
DRIVER={PostgreSQL
Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"
but this is not working, why? I do not know what these CA, BO, BI
strange paramaters.
Why is this not working, we cannot answer without the error message you
get. You probably use the wrong driver name.
As for the CA, BO, etc... they are abreviations of the keywords. You can
use either the keywords or the abreviations in your connection string,
and they are all explained here :
https://odbc.postgresql.org/docs/config-opt.html
--
Arnaud
