RE: [ext] Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

I guess this cannot be pointed out too often ;) I'm not intending to do this but the behaviour of the system with fsync=off led me to further understand that the bottleneck had to be CPU-related rather than I/O-related. Of course, I never switched off fsync on the production system. Best

Re: Work hours?

On 8/27/19 10:22 PM, Christopher Browne wrote: On Tue, Aug 27, 2019, 6:27 PM stan > wrote: I am just starting to explore the power of PostgreSQL's time and date functionality. I must say they seem very powerful. I need to write a function that, given a

Re: [ext] Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

Holtgrewe, Manuel wrote: > Switching off fsync leads to a drastic time improvement but still > higher wall-clock time for four threads. Don't do that unless you are ready to start from scratch with a new "initdb" in the case of a crash. You can do almost as good by setting "synchronous_commit =

Re: Work hours?

On Tue, Aug 27, 2019, 6:27 PM stan wrote: > I am just starting to explore the power of PostgreSQL's time and date > functionality. I must say they seem very powerful. > > I need to write a function that, given a month, and a year as input returns > the "work hours" in that month. In other words

RE: Question about password character in ECPG's connection string

Hi, Giuseppe, Thanks to response to my question! > It seems to me that ECPG documentation does not allow specifying > username and/or password in the connection string. The correct syntax > should be: > > EXEC SQL CONNECT TO "unix:postgresql://localhost/connectdb" USER > "myuser" IDENTIFIED BY

Re: Work hours?

Rob Sargent wrote: > On 8/27/19 4:59 PM, Adrian Klaver wrote: > > On 8/27/19 3:27 PM, stan wrote: > > > I am just starting to explore the power of PostgreSQL's time and date > > > functionality. I must say they seem very powerful. > > > > > > I need to write a function that, given a month, and a

Re: Work hours?

On Tue, Aug 27, 2019 at 3:27 PM stan wrote: > I need to write a function that, given a month, and a year as input returns > the "work hours" in that month. In other words something like > > 8 * the count of all days in the range Monday to Friday) within that > calendar month. This gives you all

Re: Work hours?

On 8/27/19 4:59 PM, Adrian Klaver wrote: On 8/27/19 3:27 PM, stan wrote: I am just starting to explore the power of PostgreSQL's time and date functionality. I must say they seem very powerful. I need to write a function that, given a month, and a year as input returns the "work hours" in

Re: Work hours?

On 8/27/19 5:27 PM, stan wrote: I am just starting to explore the power of PostgreSQL's time and date functionality. I must say they seem very powerful. I need to write a function that, given a month, and a year as input returns the "work hours" in that month. In other words something like 8 *

Re: Work hours?

On 8/27/19 3:27 PM, stan wrote: I am just starting to explore the power of PostgreSQL's time and date functionality. I must say they seem very powerful. I need to write a function that, given a month, and a year as input returns the "work hours" in that month. In other words something like 8 *

Work hours?

I am just starting to explore the power of PostgreSQL's time and date functionality. I must say they seem very powerful. I need to write a function that, given a month, and a year as input returns the "work hours" in that month. In other words something like 8 * the count of all days in the

Re: No warning/error trying to vacuum other session's temp table

Jerry Sievers writes: > As seen below, the command response VACUUM is output , but adding the > VERBOSe keyword results in no additional output. Yeah, per the code: /* * Silently ignore tables that are temp tables of other backends --- * trying to vacuum these will lead to great

Re: Matching pgp_sym_encrypt() and gpg2 output

On Tue, Aug 27, 2019 at 02:05:28PM -0400, Jeff Janes wrote: > On Tue, Aug 27, 2019 at 1:33 PM Bruce Momjian wrote: > > I am trying to generate output from the command-line program gpg2 that > matches the output of pgp_sym_encrypt().  gpg2 outputs: > >         $ echo 'my access

Re: Matching pgp_sym_encrypt() and gpg2 output

On Tue, Aug 27, 2019 at 1:33 PM Bruce Momjian wrote: > I am trying to generate output from the command-line program gpg2 that > matches the output of pgp_sym_encrypt(). gpg2 outputs: > > $ echo 'my access password' | tr -d '\n' | gpg2 --symmetric --batch > > --cipher-algo AES256

No warning/error trying to vacuum other session's temp table

Greetings! Spent a bit of head scratching time today responding to a case of old temp tables possibly threatening one of our reporting systems due to txid wrap. There is such an old enough backendID still live that I speculated one or more of these temp tables still possibly in use... which got

Matching pgp_sym_encrypt() and gpg2 output

I am trying to generate output from the command-line program gpg2 that matches the output of pgp_sym_encrypt(). gpg2 outputs: $ echo 'my access password' | tr -d '\n' | gpg2 --symmetric --batch > --cipher-algo AES256 --passphrase 'abc' | xxd -p | tr -d '\n'

Re: import job not working

The painful solution was to uninstall postgres and pgadmin, find all the user\appdata files etc. left behind, remove those and reinstall.  The process watcher I was missing has returned.  I have no idea what caused it to go. On 26/08/2019 16:31, Adrian Klaver wrote: On 8/26/19 1:22 PM,

RE: [ext] Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

Dear Jeff, thanks for your answer. Your question regarding CPUs pointed me into the right direction now. In my container virtualization I had the actual CPU restriction set to 2 so this explains the drop in performance (d'oh!). Actually, with using UNLOGGED tables I get constant wall-clock

Re: Recomended front ends?

On 8/27/19 8:04 AM, Daniele Varrazzo wrote: On Sun, Aug 11, 2019 at 7:49 PM Peter J. Holzer wrote: Django lets you use "unmanaged" tables, but it is quite noticeable that this isn't the primary use case. It isn't, but it's the best way to use Django for database-literate people. It's enough

Re: [ext] Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

On Tue, Aug 27, 2019 at 10:43 AM Holtgrewe, Manuel < manuel.holtgr...@bihealth.de> wrote: > Hi, > > I also tried creating the table as "UNLOGGED" which led to walwriter I/O > to drop drastically and I now get no wall-clock time increase with two > import processes but it gets slower with four. >

Re: Recomended front ends?

On Sun, Aug 11, 2019 at 7:49 PM Peter J. Holzer wrote: > Django lets you use "unmanaged" tables, but it is quite noticeable that > this isn't the primary use case. It isn't, but it's the best way to use Django for database-literate people. It's enough to ignore the Django sale pitch of the

Re: Include a connection pooler in Postgres

On 8/27/19 7:35 AM, Lev Kokotov wrote: Hello, Since most web-facing clusters need to run tools like PgBouncer or Pgpool-II, why does Postgres not come with a connection pooler of its own? Because PgBouncer and Pgpool exist, along with other options. Given an excess of developers and/or

RE: [ext] Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

Hi, I also tried creating the table as "UNLOGGED" which led to walwriter I/O to drop drastically and I now get no wall-clock time increase with two import processes but it gets slower with four. Switching off fsync leads to a drastic time improvement but still higher wall-clock time for four

Include a connection pooler in Postgres

Hello, Since most web-facing clusters need to run tools like PgBouncer or Pgpool-II, why does Postgres not come with a connection pooler of its own? - Lev

Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

On Tue, Aug 27, 2019 at 12:06 PM Holtgrewe, Manuel wrote: > iotop tells me that walwriter does not go beyond ~35MB/s so maybe this is the > culprit? Is there a way to tune walwriter I/O performance? As far as I know, walwriter is there to help background processes, so in the case it cannot keep

Re: psql \copy hanging

Interesting topic. Is there any easy way to debug, and get correct error messages? Regards, Shao On Tue, 27 Aug 2019 at 12:17, Arnaud L. wrote: > 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

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

Re: psql \copy hanging

On Tue, Aug 27, 2019 at 12:34 PM Arnaud L. wrote: > 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 would however give it a try. I would schedule a nightly

Re: Question about password character in ECPG's connection string

Hello Yusuke, Il giorno mar, 27/08/2019 alle 07.29 +, Egashira, Yusuke ha scritto: [...] > I referred the below documents. > - CONNECT of ecpg: > https://www.postgresql.org/docs/9.5/ecpg-sql-connect.html > - Connection string of libpq: >

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

Pointers towards identifying bulk import bottleneck (walwriter tuning?)

Dear all, I hope that this is the right place to ask. I have trouble identifying the bottleneck in a bulk import. I'm suspecting the bottleneck to be the walwriter. I'm using PostgreSQL 11, my configuration is at [5]. I'm running on ZFS raidz3 that can write write 300MB/s+. However, I'm only

Re: psql \copy hanging

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). > 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

Re: How to set up PostGIS to support ArcGIS, MapInfo and QGIS desktop users?

This is probably the wrong place to ask. Have a look at the POSTGis support options page. Stack Exchange has a lot of info. Also go to the particular products that you are interested in, they should have their requirements listed. You will likely also want to set

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

Re: cannot CREATE INDEX because it has pending trigger events

On Tue, Aug 27, 2019 at 9:33 AM Simon Kissane wrote: > If I swap the order of the CREATE UNIQUE INDEX and the INSERT, so the index > gets created first, the error doesn't happen. It also works removing the INITIALLY DEFERRED from the foreign key, since it seems you are creating tuples in the

Re: cannot CREATE INDEX because it has pending trigger events

On Tue, 2019-08-27 at 12:00 +1000, Simon Kissane wrote: > We have an application that works fine with Postgres 9.6, but fails > with this error when we try installing it against 11.5 > > I simplified the problem down to the following reproduce script: > > BEGIN TRANSACTION; > CREATE TABLE

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

cannot CREATE INDEX because it has pending trigger events

Hi We have an application that works fine with Postgres 9.6, but fails with this error when we try installing it against 11.5 I simplified the problem down to the following reproduce script: BEGIN TRANSACTION; CREATE TABLE resource (resource_id BIGINT NOT NULL PRIMARY KEY, resource_type

Question about password character in ECPG's connection string

Hi, I have question about connection string of ECPG's CONNECT statement. I'm using postgresql 9.5.17 with ECPG application. When I trying to connect to the database, I found that some characters as password in ECPG's connection_option cannot be accepted. Is the specification of ECPG's