Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
El día Dienstag, Mai 12, 2020 a las 05:17:33 -0700, Adrian Klaver escribió: > > > insert into swd_auftrag .. > > > > > > COMMIT? > > > > This question (if it was a question) I don't understand. > > From your original message: > > "The INSERT of 1 row into table swd_daten was OK and commit'ed (marked line) > and a later rollback (last line) seems to roll it back, at least the row > isn't in the table." > > It was not clear to me whether: > > "[1471] [12.05.2020 15:48:50:478]: ecpg_execute on line 1637: query: insert > into swd_auftrag ( setnr , aufnum , katkey , userid , seqcount ) values ( $1 > , $2 , $3 , $4 , $5 ); with 5 parameter(s) on connection sisis > [1471] [12.05.2020 15:48:50:478]: ecpg_process_output on line 1637: OK: > INSERT 0 1" > > also COMMITT(ed) or not? As I said in the original post of this thread: [1471] [12.05.2020 15:48:50:476]: ecpg_execute on line 1744: query: insert into swd_daten ( katkey , aktion , reserv , id , ansetzung , nettodaten ) values ( $1 , $2 , $3 , $4 , $5 , $6 ); with 6 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: INSERT 0 1 [1471] [12.05.2020 15:48:50:477]: ECPGtrans on line 6716: action "commit"; connection "sisis" ... i.e. the COMMIT is done in the same connection(!) right after the INSERT. There is no other ESQL/C call logged (and done) between. There is only no ACK from the ESQL/C layer about the COMMIT of ECPGtrans, but this is a fault in the ESQL/C layer code, because the ECPGtrans is logged before executing it and afterward if it's done OK no message is generated. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
El día Dienstag, Mai 12, 2020 a las 08:01:15 -0400, Tom Lane escribió:
> Matthias Apitz writes:
> > And in the log the line logged is:
> > [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK:
> > INSERT 0 1
> > What I wanted to have is:
> > [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744 of
> > swd_daten.pgc: OK: INSERT 0 1
> > i.e. have added the file name to the line number as "on line 1744 of
> > swd_daten.pgc" to not always have to think, hey in which table we're
> > with this at the moment.
>
> Not an unreasonable suggestion, but it'd be more likely to happen if
> you send in a patch ;-).
I was already thinking somemonths ago in a change (and patch proposal).
The problem is, that the generated C-code for an ESQL/C statement looks
today like this:
{ ECPGdo(__LINE__, 0, 0, NULL, 0, ECPGst_normal, "insert into swd_daten (
katkey , aktion , reserv , id , ansetzung , nettodaten ) values ( $1 , $2 ,
$3 , $4 , $5 , $6 )",
ECPGt_long,&(new_value),(long)1,(long)1,sizeof(long),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_short,&(hrec_swd_daten.aktion),(long)1,(long)1,sizeof(short),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(hrec_swd_daten.reserv),(long)2,(long)1,(2)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
...
ECPGt_char,(hrec_swd_daten.nettodaten),(long)SWD_DATEN_BUF_MAX,(long)1,(SWD_DATEN_BUF_MAX)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
#line 1745 "swd_daten.pgc"
if (sqlca.sqlcode == ECPG_NOT_FOUND) posSqlNotFound ( );
#line 1745 "swd_daten.pgc"
if (sqlca.sqlwarn[0] == 'W') posSqlWarning ( );
#line 1745 "swd_daten.pgc"
if (sqlca.sqlcode < 0) posSqlError ( );}
and should be expanded to:
{ ECPGdo(__LINE__, __FILE__, 0, 0, NULL, 0, ECPGst_normal, "insert ...
as the first argument to ECPGdo() is of type int we can not do a hack
like "__LINE__:"__FILE__ (i.e. concatenating line number and filename
into one string. We have to change the call interface function ECPGdo()
and add a string argument. This would make fail all older compiled
applications when the new shared lib having this call is installed.
Here I'm stuck with the idea.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: [EXTERNAL] Re: PostgreSQL-12 replication failover, pg_rewind fails
At Tue, 12 May 2020 17:29:50 +0200, Laurenz Albe wrote in > On Tue, 2020-05-12 at 09:40 +, Mariya Rampurawala wrote: > > > but if the target cluster ran for a long time after the divergence, > > > the old WAL files might no longer be present. In that case, they can > > > be manually copied from the WAL archive to the pg_wal directory, or > > > fetched on startup by configuring primary_conninfo or restore_command. > > > > I hit this issue every time I follow the aforementioned steps, manually as > > well as with scripts. > > How long is "long time after divergence"? Is there a way I can make some > > configuration changes so that I don’t hit this issue? > > Is there anything I must change in my restore command? As mentioned in the documentation, pg_rewind uses the WAL records startng from the last checkpoint just before the divergence point. The divergence point is shown as the follows in your log messages. > pg_rewind: servers diverged at WAL location 6/B9D8 on timeline 53 pg_rewind scans backward starting from that location to find a checkpoint record, which is the oldest WAL record pg_rewind needs. As you see it is not a matter of calculation. There's no other way than actually restoring WAL segment files to read and try finding. > What you can do is to use a higher value for "wal_keep_segments". > Then PostgreSQL will keep around that number of old WAL segments, > which increases the chance for "pg_rewind" to succeed. So this is one effective way to reduce the chance to lose required WAL (segment) files. On PG12, an easy way to automatically restore all required WAL files would be restoring the WAL file every time pg_rewind complains that it is missing. Or, you could use pg_waldump to find a checkpoint record. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
On 5/12/20 4:52 PM, Matthias Apitz wrote: El día Dienstag, Mai 12, 2020 a las 12:30:17 -0700, Adrian Klaver escribió: On 5/12/20 12:14 PM, Matthias Apitz wrote: To answer also the question of Adrian Klaver: The database in question has ~400 tables and the ESQL/C application has for each table its own ESQL/C source file. It would be possible but a nightmare to share the code and it's better to discuss the problem based on the ESQL/c log file or I have to write some small example code to reproduce the problem. The line numbers are relative to those source files (and it's a pitty that the name of the source file is not logged, onle the line numbers). Yeah, but there is a one:one mapping of table:source file and you know the table, so you should be able to find the source at the line number. Or am I missing something? Yes. The table here is swd_daten, the corresponding .pgc file is swd_daten.pgc. And in the log the line logged is: [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: INSERT 0 1 So what is happening before, during and after that line? What I wanted to have is: [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744 of swd_daten.pgc: OK: INSERT 0 1 i.e. have added the file name to the line number as "on line 1744 of swd_daten.pgc" to not always have to think, hey in which table we're with this at the moment. Also, from OP did: insert into swd_auftrag .. COMMIT? This question (if it was a question) I don't understand. From your original message: "The INSERT of 1 row into table swd_daten was OK and commit'ed (marked line) and a later rollback (last line) seems to roll it back, at least the row isn't in the table." It was not clear to me whether: "[1471] [12.05.2020 15:48:50:478]: ecpg_execute on line 1637: query: insert into swd_auftrag ( setnr , aufnum , katkey , userid , seqcount ) values ( $1 , $2 , $3 , $4 , $5 ); with 5 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:478]: ecpg_process_output on line 1637: OK: INSERT 0 1" also COMMITT(ed) or not? matthias -- Adrian Klaver adrian.kla...@aklaver.com
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
Matthias Apitz writes: > And in the log the line logged is: > [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: > INSERT 0 1 > What I wanted to have is: > [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744 of > swd_daten.pgc: OK: INSERT 0 1 > i.e. have added the file name to the line number as "on line 1744 of > swd_daten.pgc" to not always have to think, hey in which table we're > with this at the moment. Not an unreasonable suggestion, but it'd be more likely to happen if you send in a patch ;-). regards, tom lane
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
El día Dienstag, Mai 12, 2020 a las 12:30:17 -0700, Adrian Klaver escribió: > On 5/12/20 12:14 PM, Matthias Apitz wrote: > > > > To answer also the question of Adrian Klaver: > > > > The database in question has ~400 tables and the ESQL/C application has > > for each table its own ESQL/C source file. It would be possible but a > > nightmare to share the code and it's better to discuss the problem based > > on the ESQL/c log file or I have to write some small example code to > > reproduce the problem. The line numbers are relative to those source > > files (and it's a pitty that the name of the source file is not logged, > > onle the line numbers). > > Yeah, but there is a one:one mapping of table:source file and you know the > table, so you should be able to find the source at the line number. Or am I > missing something? Yes. The table here is swd_daten, the corresponding .pgc file is swd_daten.pgc. And in the log the line logged is: [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: INSERT 0 1 What I wanted to have is: [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744 of swd_daten.pgc: OK: INSERT 0 1 i.e. have added the file name to the line number as "on line 1744 of swd_daten.pgc" to not always have to think, hey in which table we're with this at the moment. > Also, from OP did: > > insert into swd_auftrag .. > > COMMIT? This question (if it was a question) I don't understand. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: Upgrade Process Says "The database server was not shut down cleanly" but it was
On 5/12/20 2:51 PM, TalGloz wrote: Adrian Klaver-4 wrote On 5/11/20 2:45 PM, TalGloz wrote: Well I tried your steps, both Postgres 10 and 12 are in perfect running conditions and were shut down using the "systemctl" command. When I execute: /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/ --new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var/lib/pgsql/12/data --old-options '-c config_file=/var/lib/pgsql/10/data/postgresql.conf' --new-options '-c config_file=/var/lib/pgsql/12/data/postgresql.conf' --check I get: Performing Consistency Checks - Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for tables WITH OIDSok Checking for invalid "sql_identifier" user columns ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok *Clusters are compatible* But if I execute the same command right after that without the "--check" flag I get: Performing Consistency Checks - Checking cluster versions ok The target cluster was not shut down cleanly. Failure, exiting Both of the servers were still shut down. If I start form the beginning with a fresh Prostgres 12 installation and execute the upgrade command right away without the "--check" flag I get the problem described in my original post. How are you installing Postgres? Are the 10 and 12 packages coming from the same location? Best regards, TalGlo -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Adrian Klaver adrian.klaver@ This is my output for "ps ax| grep post" it is always the same after shunting down the services. 11232 pts/0S+ 0:00 grep --color=auto post I've used the same installation steps for both of the instances: 1. Add the PostgreSQL Yum Repository (needed to be done once) 2. Install the desired Postgres instance using "sudo dnf install postgresqlXX-*" Have you tried the upgrade w/o the --options switches? Do the regular Postgres logs show anything? Have the systemctl scripts been changed? And wild hunch from another thread: "Is there a significant difference in Memory settings between 9.5 and 12" In particular this message: https://www.postgresql.org/message-id/CAEaSS0YDpb_L1Ve%2B4aNhNz7nSN5M%2BEpT9fV8AUuEYnY2tDtuDw%40mail.gmail.com See bottom of message for what OP says about sysctl. Best regards, TalGloz -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Adrian Klaver adrian.kla...@aklaver.com
Re: Enforcing uniqueness on [real estate/postal] addresses
On 5/12/20 1:55 PM, Peter Devoy wrote: Is is possible to have two entries which have the same address_identifier_general, street and postcode, but different descriptions? Unfortunately, yes. The data comes from gov't systems to regulate the development/alteration of arbitrary pieces of property and those pieces do not always have a postal address. E.g. a farmer may one year apply to erect a wind turbine in "field north of Foo Cottage" and the next year apply to demolish "barnhouse west of Foo Cottage". Having farmed myself, letting a farmer define a property is a questionable practice:) Now, I know what you are thinking, there is a normalization opportunity and you may well be right. However, the problem does exist in some of the other fields too and I am already facing a fair amount of join complexity in my schema so I am trying to figure out my options :) (What is an address_identifier_general, btw?) Address identifier composed by numbers and/or characters. I'm using the terminology from the EU's "INSPIRE Data Specification on Addresses" Guidelines. Took a quick scan through above, that was mind numbing. I did see that there are provisions for geographic reference grids. Is that an option? I haven't yet had the opportunity to try out the above suggestions but I will post again when I have. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Enforcing uniqueness on [real estate/postal] addresses
On Tue, 12 May 2020, Peter Devoy wrote: Is is possible to have two entries which have the same address_identifier_general, street and postcode, but different descriptions? Unfortunately, yes. The data comes from gov't systems to regulate the development/alteration of arbitrary pieces of property and those pieces do not always have a postal address. E.g. a farmer may one year apply to erect a wind turbine in "field north of Foo Cottage" and the next year apply to demolish "barnhouse west of Foo Cottage". I'm in the US but recently read (in The Economist, I think) that house numbers in the UK are sometimes denied by the local government and the owner told to select a name for the property. Can you have a primary key for the property descrption, e.g., "Foo Cottage" and another (related) column for objects associated with it; e.g., "North field" and "West barnhouse?" Similar to an apartment (flat) number in a multifamily dwelling. Rich
Re: Upgrade Process Says "The database server was not shut down cleanly" but it was
Adrian Klaver-4 wrote > On 5/11/20 2:45 PM, TalGloz wrote: >> Well I tried your steps, both Postgres 10 and 12 are in perfect running >> conditions and were shut down using the "systemctl" command. When I >> execute: >> >> /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/ >> --new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/lib/pgsql/10/data >> --new-datadir=/var/lib/pgsql/12/data --old-options '-c >> config_file=/var/lib/pgsql/10/data/postgresql.conf' --new-options '-c >> config_file=/var/lib/pgsql/12/data/postgresql.conf' --check >> >> I get: >> >> Performing Consistency Checks >> - >> Checking cluster versions >> ok >> Checking database user is the install user ok >> Checking database connection settings ok >> Checking for prepared transactions ok >> Checking for reg* data types in user tables ok >> Checking for contrib/isn with bigint-passing mismatch ok >> Checking for tables WITH OIDSok >> Checking for invalid "sql_identifier" user columns ok >> Checking for presence of required libraries ok >> Checking database user is the install user ok >> Checking for prepared transactions ok >> >> *Clusters are compatible* >> >> But if I execute the same command right after that without the "--check" >> flag I get: >> >> Performing Consistency Checks >> - >> Checking cluster versions ok >> >> The target cluster was not shut down cleanly. >> Failure, exiting >> >> >> Both of the servers were still shut down. If I start form the beginning >> with >> a fresh Prostgres 12 installation and execute the upgrade command right >> away >> without the "--check" flag I get the problem described in my original >> post. > > How are you installing Postgres? > > Are the 10 and 12 packages coming from the same location? > >> >> Best regards, >> TalGlo >> >> >> >> -- >> Sent from: >> https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html >> >> > > > -- > Adrian Klaver > adrian.klaver@ This is my output for "ps ax| grep post" it is always the same after shunting down the services. 11232 pts/0S+ 0:00 grep --color=auto post I've used the same installation steps for both of the instances: 1. Add the PostgreSQL Yum Repository (needed to be done once) 2. Install the desired Postgres instance using "sudo dnf install postgresqlXX-*" Best regards, TalGloz -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Enforcing uniqueness on [real estate/postal] addresses
On Tue, May 12, 2020 at 09:55:56PM +0100, Peter Devoy wrote: > >Is is possible to have two entries which have the same > >address_identifier_general, street and postcode, but different > >descriptions? > > Unfortunately, yes. The data comes from gov't systems to > regulate the development/alteration of arbitrary pieces of property and > those pieces do not always have a postal address. E.g. a farmer may > one year apply to erect a wind turbine in "field north of Foo Cottage" > and the next year apply to demolish "barnhouse west of Foo Cottage". LAT/LON ? https://plus.codes/ ? Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Enforcing uniqueness on [real estate/postal] addresses
>Is is possible to have two entries which have the same >address_identifier_general, street and postcode, but different >descriptions? Unfortunately, yes. The data comes from gov't systems to regulate the development/alteration of arbitrary pieces of property and those pieces do not always have a postal address. E.g. a farmer may one year apply to erect a wind turbine in "field north of Foo Cottage" and the next year apply to demolish "barnhouse west of Foo Cottage". Now, I know what you are thinking, there is a normalization opportunity and you may well be right. However, the problem does exist in some of the other fields too and I am already facing a fair amount of join complexity in my schema so I am trying to figure out my options :) >(What is an address_identifier_general, btw?) Address identifier composed by numbers and/or characters. I'm using the terminology from the EU's "INSPIRE Data Specification on Addresses" Guidelines. I haven't yet had the opportunity to try out the above suggestions but I will post again when I have.
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
On 5/12/20 12:14 PM, Matthias Apitz wrote: To answer also the question of Adrian Klaver: The database in question has ~400 tables and the ESQL/C application has for each table its own ESQL/C source file. It would be possible but a nightmare to share the code and it's better to discuss the problem based on the ESQL/c log file or I have to write some small example code to reproduce the problem. The line numbers are relative to those source files (and it's a pitty that the name of the source file is not logged, onle the line numbers). Yeah, but there is a one:one mapping of table:source file and you know the table, so you should be able to find the source at the line number. Or am I missing something? Also, from OP did: insert into swd_auftrag .. COMMIT? El día Dienstag, Mai 12, 2020 a las 11:54:40 -0400, Tom Lane escribió: Matthias Apitz writes: We're facing in our ESQL/C written application a situation where a commit'ed INSERT into a table is rolled back. Kind of hard to believe ... is there any sign of distress in the postmaster log? No. Nothing which points to this. I have here the ESQL/C logging of the problem: ... [1471] [12.05.2020 15:48:50:476]: ecpg_execute on line 1744: query: insert into swd_daten ( katkey , aktion , reserv , id , ansetzung , nettodaten ) values ( $1 , $2 , $3 , $4 , $5 , $6 ); with 6 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: INSERT 0 1 [1471] [12.05.2020 15:48:50:477]: ECPGtrans on line 6716: action "commit"; connection "sisis" ^^^ It's striking that this log shows a server ack of the INSERT, but no server ack of the COMMIT. Maybe that's just an oversight in the ESQL/C logging logic, but I wonder what's actually getting to the server. You might try I looked into the source and the ECPGtrans ... is logged before its execution in the source interfaces/ecpg/ecpglib/misc.c and after the execution only an error condition would be logged. As there is only the line of the COMMIT w/o anything else, it seems to be executed fine. enabling log_statement = all so you can get a trace of what the server thinks is happening. I will do so, but would have to find a time window for this to not be swamped by the logs. Thanks for your reply to both, Adiran and Tom. matthias -- Adrian Klaver adrian.kla...@aklaver.com
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
To answer also the question of Adrian Klaver: The database in question has ~400 tables and the ESQL/C application has for each table its own ESQL/C source file. It would be possible but a nightmare to share the code and it's better to discuss the problem based on the ESQL/c log file or I have to write some small example code to reproduce the problem. The line numbers are relative to those source files (and it's a pitty that the name of the source file is not logged, onle the line numbers). El día Dienstag, Mai 12, 2020 a las 11:54:40 -0400, Tom Lane escribió: > Matthias Apitz writes: > > We're facing in our ESQL/C written application a situation where a > > commit'ed INSERT into a table is rolled back. > > Kind of hard to believe ... is there any sign of distress in the > postmaster log? No. Nothing which points to this. > > > I have here the ESQL/C > > logging of the problem: > > > ... > > [1471] [12.05.2020 15:48:50:476]: ecpg_execute on line 1744: query: insert > > into swd_daten ( katkey , aktion , reserv , id , ansetzung , nettodaten ) > > values ( $1 , $2 , $3 , $4 , $5 , $6 ); with 6 parameter(s) on > > connection sisis > > [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: > > INSERT 0 1 > > [1471] [12.05.2020 15:48:50:477]: ECPGtrans on line 6716: action "commit"; > > connection "sisis" > > > > ^^^ > > It's striking that this log shows a server ack of the INSERT, but no server > ack of the COMMIT. Maybe that's just an oversight in the ESQL/C logging > logic, but I wonder what's actually getting to the server. You might try I looked into the source and the ECPGtrans ... is logged before its execution in the source interfaces/ecpg/ecpglib/misc.c and after the execution only an error condition would be logged. As there is only the line of the COMMIT w/o anything else, it seems to be executed fine. > enabling log_statement = all so you can get a trace of what the server > thinks is happening. I will do so, but would have to find a time window for this to not be swamped by the logs. Thanks for your reply to both, Adiran and Tom. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: System column xmin makes anonymity hard
Johannes. On Tue, May 12, 2020 at 8:05 PM Johannes Linke wrote: > since 9.4, VACUUM FREEZE just sets a flag bit instead of overwriting xmin > with FrozenTransactionId [1]. This makes it harder to build applications with > a focus on data reduction. > We have an app that lets people anonymously vote on stuff exactly once. So we > save the vote in one table without any explicit connection to the voting > user, and separate from that a flag that this person gave their vote. That > has to happen in the same transaction for obvious reasons, but now the xmin > of those two data points allows to connect them and to de-anonymize the vote. > We can of course obfuscate this connection, but our goal is to not keep this > data at all to make it impossible to de-anonymize all existing votes even > when gaining access to the server. The best idea we had so far is more of a > workaround: Do dummy updates to large parts of the vote table on every insert > so lots of tuples have the same xmin, and them VACUUMing.[2] And even without the xmin someone could cump ctid and correlate them if you are not careful. You problem is going to be hard to solve without taking extra steps. I think doing a transaction which moves all the votes for period ( using insert into with the result of a delete returning ) and then inserts them back ( with some things like a insert into of a select order by random ) may work ( you may even throw a shuffled flg along the way ). An then throw in vacuum so next batch of inserts overwrites the freed space. But for someone with the appropiate access to the system, partial deanonimization is possible unless you take very good measures. Think of it, here in spain we use ballot boxes. But voter order is recorded ( they do double entry check, you get searched in an alphabetic list, your name is copied on a time ordered list, and your position on the list recorded in the alphabetic one, all in paper, nice system, easy to audit, hard to cheat ). If you can freeze time, you can carefully pick up votes from the box and partially correlate them with the list, even with boxes much larger than the voting envelopes they tend to stack with a nice order. And this is with papers, computers are much better on purposelessly ordering everything because it is easier to do it this way. > Does anyone have a suggestion better than this? Is there any chance this > changes anytime soon? Should I post this to -hackers? Something which may be useful is to use a stagging table for newly inserted votes and move them in batches, shuffling them, to a more permanent one periodically, ad use a view to joing them. You can even do that with some fancy partiotioning and an extra field. And move some users already-voted flags too, on a different transaction. Doing some of these things and adding some old votes to the moving sets should make the things difficult to track, but it all depends on how hard your anonimization requirements are ( I mean, the paper system I've described leaves my vote perfectly identificable when I've just voted, but it is regarded as a non issue in general, and I suspect any system you can think leaves the last vote identifiable for a finite amount of time ). In general, move data around, in single transactions so you do not lose anything, like shaking a ballot box periodically ( but ensure the lid is properly taped first ). Francisco Olarte.
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 10:55 PM Tory M Blue wrote:
>
>
> On Mon, May 11, 2020 at 9:01 PM Thomas Munro
> wrote:
>
>> On Tue, May 12, 2020 at 2:52 PM Tory M Blue wrote:
>> > It took the change but didn't help. So 10GB of shared_buffers in 12 is
>> still a no go. I'm down to 5GB and it works, but this is the same hardware,
>> the same exact 9.5 configuration. So I'm missing something. WE have not had
>> to mess with kernel memory settings since 9.4, so this is an odd one.
>> >
>> > I'll keep digging, but i'm hesitant to do my multiple TB db's with half
>> of their shared buffer configs, until I understand what 12 is doing
>> differently than 9.5
>>
>> Which exact version of 9.5.x are you coming from? What's the exact
>> error message on 12 (you showed the shared_memory_type=sysv error, but
>> with the default value (mmap) how does it look)? What's your
>> huge_pages setting?
>>
>
> 9.5-20
> postgresql95-9.5.20-2PGDG.rhel7.x86_64
> postgresql95-contrib-9.5.20-2PGDG.rhel7.x86_64
> postgresql95-libs-9.5.20-2PGDG.rhel7.x86_64
> postgresql95-server-9.5.20-2PGDG.rhel7.x86_64
>
> I don't use huge_pages
>
> And this error is actually from the default mmap
>
> May 08 12:33:58 qdb01.prod.ca postmaster[8790]: < 2020-05-08 12:33:58.324
> PDT >HINT: This error usually means that PostgreSQL's request for a
> shared memory segment exceeded available memory, swap space, or huge pages.
> To reduce the request size (currently 11026235392 bytes), reduce
> PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or
> max_connections.
>
> The above error is with 12 trying to start with shared_buffers = 10GB...
>
> 9.5 starts fine with the same configuration file. That kind of started
> me down this path.
>
> And just to repeat. Same exact hardware, same kernel, nothing more than
> installing the latest postgres12, copying my config files from 9.5 to 12
> and running the pg_upgrade.
>
> 9.5 has been running for years with the same configuration file, so
> something changed somewhere along the line that is preventing 12 to start
> with the same config file. And the allocation error is with either the
> sysv or mman on 12. (will start with 5GB allocated, but not 10GB, on a 15GB
> box (dedicated postgres server).
>
>
>> Can you reproduce the problem with a freshly created test cluster? As
>> a regular user, assuming regular RHEL packaging, something like
>> /usr/pgsql-12/bin/initdb -D test_pgdata, and then
>> /usr/pgsql-12/bin/postgres -D test_pgdata -c shared_buffers=10GB (then
>> ^C to stop it). If that fails to start in the same way, it'd be
>> interesting to see the output of the second command with strace in
>> front of it, in the part where it allocates shared memory. And
>> perhaps it'd be interesting to see the same output with
>> /usr/pgsql-9.5/bin/XXX (if you still have the packages). For example,
>> on my random dev laptop that looks like:
>>
>> openat(AT_FDCWD, "/proc/meminfo", O_RDONLY) = 6
>> fstat(6, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
>> read(6, "MemTotal: 16178852 kB\nMemF"..., 1024) = 1024
>> read(6, ":903168 kB\nShmemHugePages: "..., 1024) = 311
>> close(6)= 0
>> mmap(NULL, 11016339456, PROT_READ|PROT_WRITE,
>> MAP_SHARED|MAP_ANONYMOUS|MAP_HUGETLB, -1, 0) = -1 ENOMEM (Cannot
>> allocate memory)
>> mmap(NULL, 11016003584, PROT_READ|PROT_WRITE,
>> MAP_SHARED|MAP_ANONYMOUS, -1, 0) = 0x7ff74e579000
>> shmget(0x52e2c1, 56, IPC_CREAT|IPC_EXCL|0600) = 3244038
>> shmat(3244038, NULL, 0) = 0x7ff9df5ad000
>>
>> The output is about the same on REL9_5_STABLE and REL_12_STABLE for
>> me, only slightly different sizes. If that doesn't fail in the same
>> way on your system with 12, perhaps there are some more settings from
>> your real clusters required to make it fail. You could add them one
>> by one with -c foo=bar or in the throw away
>> test_pgdata/postgresql.conf, and perhaps that process might shed some
>> light?
>>
>> I was going to ask if it might be a preloaded extension that is asking
>> for gobs of extra memory in 12, but we can see from your "Failed
>> system call was shmget(key=5432001, size=11026235392, 03600)" that
>> it's in the same ballpark as my total above for shared_buffers=10GB.
>>
>
> Be more than happy to test this out. I'll see what I can pull tomorrow and
> provide some dataz :) I know it's not ideal to use the same config file,
> I know that various things are added or changed (usually added) but the
> defaults are typically safe. But after sometime dialing in the settings for
> our use case, I've just kind of kept moving them forward.
>
> But let me do some more testing tomorrow (since I'm trying to get to the
> bottom of this, before I attempt my big DB upgrades). So I'll spend some
> time testing and see if I can't get similar "failures/challenges"? and go
> from there.
>
> Appreciate the ideas!
>
> Tory
>
Well that is interesting. Built a new system, installed 9.5 and 12, moved
my config file
System column xmin makes anonymity hard
Hi! since 9.4, VACUUM FREEZE just sets a flag bit instead of overwriting xmin with FrozenTransactionId [1]. This makes it harder to build applications with a focus on data reduction. We have an app that lets people anonymously vote on stuff exactly once. So we save the vote in one table without any explicit connection to the voting user, and separate from that a flag that this person gave their vote. That has to happen in the same transaction for obvious reasons, but now the xmin of those two data points allows to connect them and to de-anonymize the vote. We can of course obfuscate this connection, but our goal is to not keep this data at all to make it impossible to de-anonymize all existing votes even when gaining access to the server. The best idea we had so far is more of a workaround: Do dummy updates to large parts of the vote table on every insert so lots of tuples have the same xmin, and them VACUUMing.[2] Does anyone have a suggestion better than this? Is there any chance this changes anytime soon? Should I post this to -hackers? Any input would be appreciated! Johannes [1] See the blue box on this page: https://www.postgresql.org/docs/current/routine-vacuuming.html [2] The ctid poses a similar problem. We think we have fixed that by making the primary key of the vote table a UUID, after which we periodically cluster the whole table.
Re: Enforcing uniqueness on [real estate/postal] addresses
On 2020-05-12 10:49:22 +1000, Tim Cross wrote: > Peter Devoy writes: > > I need to store addresses for properties (as in real estate) so in my > > naivety I created a unique constraint like this: > > > > ALTER TABLE properties > > ADD CONSTRAINT is_unique_address > > UNIQUE ( > > description, --e.g. Land north of Foo Cottage > > address_identifier_general, > > street, > > postcode > > ); > > > > Of course, if any of the fields are NULL (which they often are) I end > > up with duplicates. [...] > > Personally, I don't like the idea of using empty strings just to avoid > having nulls. This is probably a personal preference, but for me null > and '' are quite different. A null indicates an unknown - we don't know > what the value is. An empty string i.e. '' means there is no value (i.e. > we know it has no value). The difference is quite subtle and may not > seem relevant. It may not be or it may be or it may become relevant in > the future. General rule of thumb for me is that my model should reflect > the known information and should always avoid any data transformation or > mapping which reduces the known information. > > I would step back a bit and think about why/what constraint you really > need and what needs to be unique. The first field which jumps out for me > is description. Is this really a unique value? As the coonstraint stands, it isn't. Only description, address_identifier_general, street and postcode together are unique. I'd therefore ask the question in the other direction: Is is possible to have two entries which have the same address_identifier_general, street and postcode, but different descriptions? What does that mean? To different properties which happen to be at the same place or two descriptions for the same property? (What is an address_identifier_general, btw?) I agree with the rest of posting. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 11:09 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, May 11, 2020, David G. Johnston > wrote: > >> Repost, edited subject by mistake... >> >> On Monday, May 11, 2020, Tory M Blue wrote: >>> >>> And just to repeat. Same exact hardware, same kernel, nothing more than >>> installing the latest postgres12, copying my config files from 9.5 to 12 >>> and running the pg_upgrade. >>> >> >> You’ll want to remove the pg_upgrade from the equation and try v12 >> > > Sorry...if you copied the config to v12 before the upgrade and the upgrade > worked that suggests that v12 booted up at some point with the > configuration, no? Does pg_upgrade do something special? > > David J > Not entirely sure I follow, and it may be that I confused the issue. 9.5 running for years, run the upgrade, and migrate my config files. 12 won't start without bumping the shared_buffers down. 12 won't start with "my" original config files. I'm going to do native 9.5 and 12 installs on the same piece of hardware, no data migration, no pg_upgrade just to see if I can get 12 to start with my current configuration. I'll try your suggestions from last night as well, see if setting via command line will give us more dataz. Tory > >
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
Matthias Apitz writes: > We're facing in our ESQL/C written application a situation where a > commit'ed INSERT into a table is rolled back. Kind of hard to believe ... is there any sign of distress in the postmaster log? > I have here the ESQL/C > logging of the problem: > ... > [1471] [12.05.2020 15:48:50:476]: ecpg_execute on line 1744: query: insert > into swd_daten ( katkey , aktion , reserv , id , ansetzung , nettodaten ) > values ( $1 , $2 , $3 , $4 , $5 , $6 ); with 6 parameter(s) on > connection sisis > [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: > INSERT 0 1 > [1471] [12.05.2020 15:48:50:477]: ECPGtrans on line 6716: action "commit"; > connection "sisis" > > ^^^ It's striking that this log shows a server ack of the INSERT, but no server ack of the COMMIT. Maybe that's just an oversight in the ESQL/C logging logic, but I wonder what's actually getting to the server. You might try enabling log_statement = all so you can get a trace of what the server thinks is happening. regards, tom lane
Re: Hash partitioning, what function is used to compute the hash?
On Mon, May 11, 2020 at 3:13 PM Alvaro Herrera wrote: > On 2020-May-11, Michael Lewis wrote: > > > Afaik, hash partition doesn't have real world expected use cases just > yet. > > I don't think I agree with this assertion. > I didn't mean to be critical at all, or even make a statement of fact. Just sharing my impression. I typically view partitioning from the perspective of multi-tenancy and with the restrictions on primary keys & partition keys, I can't typically use partitioning except for audit logging tables and then range partitions make the most sense there because of doing backups and dropping the oldest data. Perhaps it is just that hash has never been the right tool for my use cases. I'd love to know some real life examples of when hash partitioning was the best option.
Re: AutoVacuum and growing transaction XID's
Thanks for yous suggestions Michael and David. On Fri, May 8, 2020 at 4:11 PM Michael Lewis wrote: > autovacuum_naptime being only 5 seconds seems too frequent. A lock_timeout > might be 1-5 seconds depending on your system. Usually, DDL can fail and > wait a little time rather than lock the table for minutes and have all > reads back up behind the DDL. > > Given you have autovacuum_vacuum_cost_limit set to unlimited (seems very > odd), I'm not sure a manual vacuum freeze command on the tables with high > age would perform differently. Still, issuing a vacuum freeze and then > killing the autovacuum process might be worth trying. >
Re: [EXTERNAL] Re: PostgreSQL-12 replication failover, pg_rewind fails
On Tue, 2020-05-12 at 09:40 +, Mariya Rampurawala wrote: > > but if the target cluster ran for a long time after the divergence, > > the old WAL files might no longer be present. In that case, they can > > be manually copied from the WAL archive to the pg_wal directory, or > > fetched on startup by configuring primary_conninfo or restore_command. > > I hit this issue every time I follow the aforementioned steps, manually as > well as with scripts. > How long is "long time after divergence"? Is there a way I can make some > configuration changes so that I don’t hit this issue? > Is there anything I must change in my restore command? What you can do is to use a higher value for "wal_keep_segments". Then PostgreSQL will keep around that number of old WAL segments, which increases the chance for "pg_rewind" to succeed. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
On 5/12/20 7:53 AM, Matthias Apitz wrote: Hello, We're facing in our ESQL/C written application a situation where a commit'ed INSERT into a table is rolled back. I have here the ESQL/C logging of the problem: ... [1471] [12.05.2020 15:48:50:476]: ecpg_execute on line 1744: query: insert into swd_daten ( katkey , aktion , reserv , id , ansetzung , nettodaten ) values ( $1 , $2 , $3 , $4 , $5 , $6 ); with 6 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: INSERT 0 1 [1471] [12.05.2020 15:48:50:477]: ECPGtrans on line 6716: action "commit"; connection "sisis" ^^^ [1471] [12.05.2020 15:48:50:478]: ecpg_execute on line 1637: query: insert into swd_auftrag ( setnr , aufnum , katkey , userid , seqcount ) values ( $1 , $2 , $3 , $4 , $5 ); with 5 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:478]: ecpg_process_output on line 1637: OK: INSERT 0 1 [1471] [12.05.2020 15:48:50:478]: ECPGtrans on line 1124: action "commit"; connection "sisis" [1471] [12.05.2020 15:48:51:500]: ECPGtrans on line 6716: action "commit"; connection "sisis" [1471] [12.05.2020 15:48:51:501]: ECPGtrans on line 1222: action "rollback"; connection "sisis" ^^^ ... The INSERT of 1 row into table swd_daten was OK and commit'ed (marked line) and a later rollback (last line) seems to roll it back, at least the row isn't in the table. Any ideas? The connection is not set to AUTOCOMMIT. Hard to tell without seeing the code. Some things I see: 1) ECPGtrans on line 6716: action "commit"; connection "sisis" occurs twice. 2) ECPGtrans on line 1222: action "rollback"; connection "sisis" Comes from a line that precedes the INSERT you are interested in. Is there some sort of nesting going on? matthias -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_upgrade too slow on vacuum phase
On 5/12/20 5:27 AM, Kouber Saparev wrote:
I am trying to upgrade PostgreSQL from 9.5 to 12 using pg_upgrade.
/usr/lib/postgresql/12/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.5/main \
--new-datadir=/var/lib/postgresql/12/main \
--old-bindir=/usr/lib/postgresql/9.5/bin \
--new-bindir=/usr/lib/postgresql/12/bin \
--old-options '-c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
--link \
--jobs=16
It takes 47 minutes for the upgrade to finish (for a 28 GB database). It
hangs on two particular steps:
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Are you sure?
From here:
~/src/bin/pg_upgrade/pg_upgrade.c
prepare_new_cluster(void)
{
/*
* It would make more sense to freeze after loading the schema,
but that
* would cause us to lose the frozenids restored by the load.
We use
* --analyze so autovacuum doesn't update statistics later
*/
prep_status("Analyzing all rows in the new cluster");
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
"\"%s/vacuumdb\" %s --all --analyze %s",
new_cluster.bindir,
cluster_conn_opts(_cluster),
log_opts.verbose ? "--verbose" : "");
check_ok();
/* -- NEW -- */
start_postmaster(_cluster, true);
check_new_cluster();
report_clusters_compatible();
pg_log(PG_REPORT,
"\n"
"Performing Upgrade\n"
"--\n");
prepare_new_cluster();
stop_postmaster(false);
/*
* Destructive Changes to New Cluster
*/
copy_xact_xlog_xid();
/* New now using xids of the old system */
/* -- NEW -- */
start_postmaster(_cluster, true);
prepare_new_globals();
create_new_objects();
stop_postmaster(false);
So the analyze(and freeze) are done before the new cluster are fully
populated. Is the time being taken maybe for the loading schema/data
portion?
Which basically runs:
vacuumdb --all --analyze
vacuumdb --all --freeze
This is where all these 47 minutes are spent, yet I do not understand
neither why, nor how I can improve this part. Can I skip it somehow and
launch the vacuum manually afterwards?
Per postgresql.conf, I gave:
work_mem = 128MB
maintenance_work_mem = 8GB
max_parallel_maintenance_workers = 16
max_parallel_workers = 16
The server has 44 GB available memory, and 24 cores.
Do you have any ideas how to speed-up the entire process?
--
Kouber Saparev
--
Adrian Klaver
adrian.kla...@aklaver.com
ESQL/C: a ROLLBACK rolls back a COMMITED transaction
Hello, We're facing in our ESQL/C written application a situation where a commit'ed INSERT into a table is rolled back. I have here the ESQL/C logging of the problem: ... [1471] [12.05.2020 15:48:50:476]: ecpg_execute on line 1744: query: insert into swd_daten ( katkey , aktion , reserv , id , ansetzung , nettodaten ) values ( $1 , $2 , $3 , $4 , $5 , $6 ); with 6 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: INSERT 0 1 [1471] [12.05.2020 15:48:50:477]: ECPGtrans on line 6716: action "commit"; connection "sisis" ^^^ [1471] [12.05.2020 15:48:50:478]: ecpg_execute on line 1637: query: insert into swd_auftrag ( setnr , aufnum , katkey , userid , seqcount ) values ( $1 , $2 , $3 , $4 , $5 ); with 5 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:478]: ecpg_process_output on line 1637: OK: INSERT 0 1 [1471] [12.05.2020 15:48:50:478]: ECPGtrans on line 1124: action "commit"; connection "sisis" [1471] [12.05.2020 15:48:51:500]: ECPGtrans on line 6716: action "commit"; connection "sisis" [1471] [12.05.2020 15:48:51:501]: ECPGtrans on line 1222: action "rollback"; connection "sisis" ^^^ ... The INSERT of 1 row into table swd_daten was OK and commit'ed (marked line) and a later rollback (last line) seems to roll it back, at least the row isn't in the table. Any ideas? The connection is not set to AUTOCOMMIT. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: [EXTERNAL] Re: PostgreSQL-12 replication failover, pg_rewind fails
Hi, Thank you for the response. > but if the target cluster ran for a long time after the divergence, > the old WAL files might no longer be present. In that case, they can > be manually copied from the WAL archive to the pg_wal directory, or > fetched on startup by configuring primary_conninfo or restore_command. I hit this issue every time I follow the aforementioned steps, manually as well as with scripts. How long is "long time after divergence"? Is there a way I can make some configuration changes so that I don’t hit this issue? Is there anything I must change in my restore command? === primary_conninfo = 'user=replicator host=10.209.57.16 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' restore_command = 'scp root@10.209.56.88:/pg_backup/%f %p' === Regards, Mariya On 12/05/20, 2:15 PM, "Kyotaro Horiguchi" wrote: Hello. At Tue, 12 May 2020 06:32:30 +, Mariya Rampurawala wrote in > I am working on providing HA for replication, using automation scripts. > My set up consists on two nodes, Master and Slave. When master fails, The slave is promoted to master. But when I try to re-register the old master as slave, the pg_rewind command fails. Details below. ... > 1. Rewind again: > 2. -bash-4.2$ /usr/pgsql-12/bin/pg_rewind -D /pg_mnt/pg-12/data --source-server="host=10.209.57.17 port=5432 user=postgres dbname=postgres" > > pg_rewind: servers diverged at WAL location 6/B9D8 on timeline 53 > > pg_rewind: error: could not open file "/pg_mnt/pg-12/data/pg_wal/0035000600B9": No such file or directory > > pg_rewind: fatal: could not find previous WAL record at 6/B9D8 > > > I have tried this multiple times but always face the same error. Can someone help me resolve this? As the error message is saying, required WAL file has been removed on the old master. It is the normal behavior and described in the documentation. https://www.postgresql.org/docs/12/app-pgrewind.html > but if the target cluster ran for a long time after the divergence, > the old WAL files might no longer be present. In that case, they can > be manually copied from the WAL archive to the pg_wal directory, or > fetched on startup by configuring primary_conninfo or restore_command. So you seem to need to restore the required WAL files from archive or the current master. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
pg_upgrade too slow on vacuum phase
I am trying to upgrade PostgreSQL from 9.5 to 12 using pg_upgrade. /usr/lib/postgresql/12/bin/pg_upgrade \ --old-datadir=/var/lib/postgresql/9.5/main \ --new-datadir=/var/lib/postgresql/12/main \ --old-bindir=/usr/lib/postgresql/9.5/bin \ --new-bindir=/usr/lib/postgresql/12/bin \ --old-options '-c config_file=/etc/postgresql/9.5/main/postgresql.conf' \ --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \ --link \ --jobs=16 It takes 47 minutes for the upgrade to finish (for a 28 GB database). It hangs on two particular steps: Analyzing all rows in the new cluster ok Freezing all rows in the new clusterok Which basically runs: vacuumdb --all --analyze vacuumdb --all --freeze This is where all these 47 minutes are spent, yet I do not understand neither why, nor how I can improve this part. Can I skip it somehow and launch the vacuum manually afterwards? Per postgresql.conf, I gave: work_mem = 128MB maintenance_work_mem = 8GB max_parallel_maintenance_workers = 16 max_parallel_workers = 16 The server has 44 GB available memory, and 24 cores. Do you have any ideas how to speed-up the entire process? -- Kouber Saparev
Re: PostgreSQL-12 replication failover, pg_rewind fails
Hello. At Tue, 12 May 2020 06:32:30 +, Mariya Rampurawala wrote in > I am working on providing HA for replication, using automation scripts. > My set up consists on two nodes, Master and Slave. When master fails, The > slave is promoted to master. But when I try to re-register the old master as > slave, the pg_rewind command fails. Details below. ... > 1. Rewind again: > 2. -bash-4.2$ /usr/pgsql-12/bin/pg_rewind -D /pg_mnt/pg-12/data > --source-server="host=10.209.57.17 port=5432 user=postgres dbname=postgres" > > pg_rewind: servers diverged at WAL location 6/B9D8 on timeline 53 > > pg_rewind: error: could not open file > "/pg_mnt/pg-12/data/pg_wal/0035000600B9": No such file or > directory > > pg_rewind: fatal: could not find previous WAL record at 6/B9D8 > > > I have tried this multiple times but always face the same error. Can someone > help me resolve this? As the error message is saying, required WAL file has been removed on the old master. It is the normal behavior and described in the documentation. https://www.postgresql.org/docs/12/app-pgrewind.html > but if the target cluster ran for a long time after the divergence, > the old WAL files might no longer be present. In that case, they can > be manually copied from the WAL archive to the pg_wal directory, or > fetched on startup by configuring primary_conninfo or restore_command. So you seem to need to restore the required WAL files from archive or the current master. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
Re: create index insist on 2 workers only
Just to reply for completeness. MWM was high enough (4GB) . Apparently we were having max_worker_processes = 18 and 16 subscriptions running, leaving no room for additional processes. What an oversight. Once mwp was bumped (to 42 on 64 core machine), everything started working as expected. On Sat, Apr 25, 2020 at 5:03 AM Peter Geoghegan wrote: > On Fri, Apr 24, 2020 at 7:32 AM Radoslav Nedyalkov > wrote: > > We 're rebuilding a big table which has set parallel_workers = 6 > > system has > > max_parallel_maintenance_workers | 6 | > /var/lib/pgsql/11/data/postgresql.sumup.conf > > max_parallel_workers | 16 | > /var/lib/pgsql/11/data/postgresql.sumup.conf > > max_parallel_workers_per_gather | 4 | > /var/lib/pgsql/11/data/postgresql.sumup.conf > > > > Also session level on index restore there is > > set max_parallel_maintenance_workers = 6; > > > > Still we get only 2 parallel processes in a free of any other load > system. > > It is postgres 11.7 > > Try increasing maintenance_work_mem from the default of 64MB. MWM > constrains the number of parallel workers used. > > -- > Peter Geoghegan >
Re: PostgreSQL-12 replication failover, pg_rewind fails
Hello, Can someone please help me with the below query? Regards, Mariya From: Mariya Rampurawala Date: Sunday, 10 May 2020 at 2:55 PM To: "pgsql-general@lists.postgresql.org" , "pgsql-gene...@postgresql.org" Subject: PostgreSQL-12 replication failover, pg_rewind fails Hi, I am working on providing HA for replication, using automation scripts. My set up consists on two nodes, Master and Slave. When master fails, The slave is promoted to master. But when I try to re-register the old master as slave, the pg_rewind command fails. Details below. Non-default entries in Postgresql.conf === archive_mode = on archive_command = 'scp %p root@10.209.56.88:/pg_backup/%f' archive_cleanup_command = '/bin/ssh root@10.209.56.88 "/usr/pgsql-12/bin/pg_archivecleanup /pg_backup %r"' synchronous_standby_names = '*' listen_addresses = '*' wal_level = hot_standby max_wal_senders = 32 max_replication_slots = 8 hot_standby = on wal_log_hints = on synchronous_commit = on hot_standby_feedback = on restart_after_crash = off max_connections = 100 === Postgresql.auto.conf at standby: === primary_conninfo = 'user=replicator host=10.209.57.16 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' restore_command = 'scp root@10.209.56.88:/pg_backup/%f %p' recovery_target_timeline='latest' === Reproduction steps: 1. Start master: -bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /pg_mnt/pg-12/data/ start waiting for server to start2020-05-11 05:26:01.665 IST [23999] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit 2020-05-11 05:26:01.665 IST [23999] LOG: listening on IPv4 address "0.0.0.0", port 5432 2020-05-11 05:26:01.665 IST [23999] LOG: listening on IPv6 address "::", port 5432 2020-05-11 05:26:01.666 IST [23999] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-05-11 05:26:01.668 IST [23999] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2020-05-11 05:26:01.688 IST [23999] LOG: redirecting log output to logging collector process 2020-05-11 05:26:01.688 IST [23999] HINT: Future log output will appear in directory "log". done server started 1. Basebackup on slave: /usr/pgsql-12/bin/pg_basebackup -D /pg_mnt/pg-12/data -X stream -R -c fast -U replicator --host=10.209.57.16 --port=5432 1. Copy postgresql.auto.conf to slave data directory and touch standby.signal 2. Start slave -bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /pg_mnt/pg-12/data start waiting for server to start2020-05-11 05:29:09.422 IST [22624] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit 2020-05-11 05:29:09.422 IST [22624] LOG: listening on IPv4 address "0.0.0.0", port 5432 2020-05-11 05:29:09.422 IST [22624] LOG: listening on IPv6 address "::", port 5432 2020-05-11 05:29:09.423 IST [22624] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-05-11 05:29:09.425 IST [22624] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2020-05-11 05:29:09.442 IST [22624] LOG: redirecting log output to logging collector process 2020-05-11 05:29:09.442 IST [22624] HINT: Future log output will appear in directory "log". .. done server started 1. Check replication status on master: -bash-4.2$ psql psql (12.2) Type "help" for help. postgres=# \x Expanded display is on. postgres=# select * from pg_stat_replication; -[ RECORD 1 ]+- pid | 24988 usesysid | 16385 usename | replicator application_name | walreceiver client_addr | 10.209.57.14 client_hostname | client_port | 40802 backend_start| 2020-05-11 05:29:14.752341+05:30 backend_xmin | 588 state| streaming sent_lsn | 5/A6D8 write_lsn| 5/A6D8 flush_lsn| 5/A6D8 replay_lsn | 5/A6D8 write_lag| flush_lag| replay_lag | sync_priority| 1 sync_state | sync reply_time | 2020-05-11 05:29:54.895857+05:30 1. From a third node write large data on master #psql -h 10.209.57.16 --user=postgres psql (12.2) Type "help" for help. postgres=# \c vcs You are now connected to database "vcs" as user "postgres". vcs=# create table c1 as select generate_series(1,100); 1. Kill postgres processes on master: -bash-4.2$ ps -ef | grep post root 1860 18990 0 May08 pts/100:00:00 vim postgresql.cluster.conf root 1925 14243 0 Apr07 pts/000:00:00 su - postgres postgres 1926 1925 0 Apr07 pts/000:00:00 -bash root 7321 1 0 Mar14 ?00:00:15 /usr/libexec/postfix/master -w postfix 7350 7321 0 Mar14 ?00:00:03 qmgr -l -t unix -u postfix 10410 7321 0
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Monday, May 11, 2020, David G. Johnston wrote: > Repost, edited subject by mistake... > > On Monday, May 11, 2020, Tory M Blue wrote: >> >> And just to repeat. Same exact hardware, same kernel, nothing more than >> installing the latest postgres12, copying my config files from 9.5 to 12 >> and running the pg_upgrade. >> > > You’ll want to remove the pg_upgrade from the equation and try v12 > Sorry...if you copied the config to v12 before the upgrade and the upgrade worked that suggests that v12 booted up at some point with the configuration, no? Does pg_upgrade do something special? David J.
Re: Is there a significant difference in Memory settings between 9.5 and 12
Repost, edited subject by mistake... On Monday, May 11, 2020, Tory M Blue wrote: > > And just to repeat. Same exact hardware, same kernel, nothing more than > installing the latest postgres12, copying my config files from 9.5 to 12 > and running the pg_upgrade. > You’ll want to remove the pg_upgrade from the equation and try v12 David J.
Re: Is there a significant difference in Memory settings between 9.5 and 12.
On Monday, May 11, 2020, Tory M Blue wrote: > > > And just to repeat. Same exact hardware, same kernel, nothing more than > installing the latest postgres12, copying my config files from 9.5 to 12 > and running the pg_upgrade. > You’ll want to remove the pg_upgrade from the equation and try v12 David J.
