[GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Pete Fuller]

Hello all,

We are attempting to upgrade a production 9.2 postgres cluster to 9.5. The 
server is running Centos 7 with the Centos version - currently 9.2.15 We are 
installing the postgresql provided rpm of postgresql9.5 from the postgresql 
repo, currently 9.5.4. In testing and on our development cluster, the upgrade 
worked without issue but something is happening on our production DB that we 
can not figure out.

The 9.2 cluster is in streaming replication controlled by pacemaker. Steps we 
have been using are the following.

• verify the replica server is in a good state with up to date data, 
then put the machine in standby, stopping the 9.2 db
• Removing the recovery.conf file and starting the 9.2 server manually 
to get some stats to verify later (counts of rows from several key tables)
• Stop the 9.2 server, (/bin/pg_ctl stop -D /var/lib/pgsql/data ) and 
install the 9.5 binaries.
• init the 9.5 db
• run a pg_upgrade with the link option. The full command we are using 
is  
/usr/pgsql-9.5/bin/pg_upgrade -d /var/lib/pgsql/data -D /var/lib/pgsql/9.5/data 
-b /bin -B /usr/pgsql-9.5/bin/ --link -r -v 
• run the full vacuum analyze script that pg_upgrade generated

What we are finding in testing is we get thru the upgrade process, start the 
9.5 server manually to compare the row counts we collected immediately before 
the upgrade and we are seeing different data, sometimes drastically. This data 
includes missing user accounts, etc, that is not perishable data.

For example - 9.2 standalone before running pg_upgrade gives this information
tracks=# select count(*) from users;
 count
---
 13945
(1 row)

tracks=# select count(*) from sessions;
 count
---
   559
(1 row)

PG 9.5 after the upgrade - same machine, upgraded database

tracks=# select count(*) from sessions;
---
   155
(1 row)

tracks=# select count(*) from users;
 count
---
 13157
(1 row)


We have enabled verbose logging and have reviewed it but are seeing no errors 
in the migration. The 9.5 server starts without complaint and if we did not do 
these manual checks we would not see a problem. We have attempted this upgrade 
on this machine repeatedly in prepping for our maintenance windows, and on 
occasion our manual checks have shown good numbers.  At this point, though, it 
has failed silently enough times that I have little confidence in the data even 
if our rudimentary checks do show identical counts after the upgrade. 

Has anyone else seen this issue? 

The only 'hack' during the upgrade that isn't straight from the 
postgres_upgrade man page is to handle a renamed option in postgresql that 
cents back ported - unix_socket_directories. Using the rename of pg_ctl that is 
referenced in the top answer here to force a couple variables into the centos 
provided pg_ctl binary - 
http://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configuration-parameter-unix-socket-directory


Any information or suggestions would be helpful at this point. 






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] regexp_replace double quote

[Михаил]

Hi!

I need to escape double quotes only:
test=# select regexp_replace('"""{Performer,"Boomwacker ""a""
Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g');
 regexp_replace
-
 """{Performer,"Boomwacker \"a"" Recording\"}"""

This is unexpected result.

But when added one symbol to ""a"" the result is right:
test=# select regexp_replace('"""{Performer,"Boomwacker ""a1""
Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g');
  regexp_replace
--
 """{Performer,"Boomwacker \"a1\" Recording\"}"""


I had tested on versions:
 PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (Gentoo
4.8.3 p1.1, pie-0.5.9) 4.8.3, 64-bit

And:
 PostgreSQL 9.5.3 on x86_64-apple-darwin, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc.
build 5658)
 (LLVM build 2336.11.00), 64-bit

And:
PostgreSQL 9.4.7 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

What`s wrong?

-- 
---
Regards,
Mikhail


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace double quote

[hubert depesz lubaczewski]

On Mon, Aug 15, 2016 at 06:27:06PM +0500, Михаил wrote:
> I need to escape double quotes only:
> test=# select regexp_replace('"""{Performer,"Boomwacker ""a"" 
> Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g');
>  regexp_replace
> -
>  """{Performer,"Boomwacker \"a"" Recording\"}"""
> 
> This is unexpected result.
> 
> But when added one symbol to ""a"" the result is right:
> test=# select regexp_replace('"""{Performer,"Boomwacker ""a1"" 
> Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g');
>   regexp_replace
> --
>  """{Performer,"Boomwacker \"a1\" Recording\"}"""

This is because when finding first "", "a" that is afterwards get
assigned to \2. and thus is already "used", and can't be part of
match for the second "".

What will solve the problem is to use lookahead, like:
$ select regexp_replace('"""{Performer,"Boomwacker ""a"" Recording""}"""', 
'([^"])"{2}(?=[^"])', '\1\"', 'g');
 regexp_replace  
-
 """{Performer,"Boomwacker \"a\" Recording\"}"""
(1 row)

because then the part inside (?=...) is not "used", and can be used for next
match.

Not sure if I'm clear, but hopefully you'll understand what I'm trying to
explain :)

Best regards,

depesz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace double quote

[David G. Johnston]

On Mon, Aug 15, 2016 at 9:27 AM, Михаил  wrote:

> Hi!
>
> I need to escape double quotes only:
> test=# select regexp_replace('"""{Performer,"Boomwacker ""a""
> Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g');
>  regexp_replace
> -
>  """{Performer,"Boomwacker \"a"" Recording\"}"""
>

What is the goal you are trying to accomplish​.  Its possible to do what
you ask but only if no other solution is feasible.


> This is unexpected result.
>
> But when added one symbol to ""a"" the result is right:
> test=# select regexp_replace('"""{Performer,"Boomwacker ""a1""
> Recording""}"""', '
> ​​
> ([^"])"{2}([^"])', '\1\"\2', 'g');
>   regexp_replace
> --
>  """{Performer,"Boomwacker \"a1\" Recording\"}"""
>
>
<​
​
([^"])"{2}([^"])> on < ""a""> consumes < ""a>​ leaving <""> which doesn't
match your pattern since there is nothing before the double-quote to
satisfy the [^"]

See depesz's simultaneous post for the solution using look-ahead.

David J.

[GENERAL] ERROR: MultiXactId XXXXX has not been created yet -- apparent wraparound

[Ioana Danes]

Hello All,

I am running postgres 9.4.8 on centos 7 and few days ago I started to get
this error MultiXactId 32766 has not been created yet -- apparent
wraparound in 2 instances.

1. On one database server during the nightly task that does the "vacuum
analyze". I upgraded to postgres 9.4.9 as there is a reference to a bug
fixed with a reference to this error but I am still getting the same error
when I vacuum analyze. This database was created using pg_dump and
pg_restore (not pg_upgrade) from the previous installation which was still
postgres 9.4.6 but on SLES instead of CentOS. I only have one postgres
version installed on the server.

Autovacuum is turned on and I also have a task that runs vacuum analyze
every night.

postgresql94-9.4.9-1PGDG.rhel7.x86_64
postgresql94-plperl-9.4.9-1PGDG.rhel7.x86_64
postgresql94-libs-9.4.9-1PGDG.rhel7.x86_64
postgresql94-server-9.4.9-1PGDG.rhel7.x86_64
postgresql94-contrib-9.4.9-1PGDG.rhel7.x86_64

psql (9.4.9)
=# vacuum analyze;
ERROR:  MultiXactId 32766 has not been created yet -- apparent wraparound

This server only runs since June 2016.

2. The second instance of this error is on a new database server during
pg_restore on few CREATE INDEX, PRIMARY KEYS and FOREIGN KEYS statements
(postgres 9.4.8 on centos 7). In this case I dropped and recreated the
database and the second try succeeded without errors.

postgresql-14.csv:2016-08-14 05:33:59.753
CST,"postgres","test",19555,"[local]",57b055d4.4c63,11,"CREATE
INDEX",2016-08-14 05:28:20 CST,1/27230,5381,ERROR,XX000,"MultiXactId
1667854355 has not been created yet -- apparent wraparound",,"CREATE
INDEX...;


Please let me know if I should provide more info.

Thank you in advance,
Ioana

Re: [GENERAL] ERROR: MultiXactId XXXXX has not been created yet -- apparent wraparound

[Adrian Klaver]

On 08/15/2016 06:48 AM, Ioana Danes wrote:

Hello All,

I am running postgres 9.4.8 on centos 7 and few days ago I started to
get this error MultiXactId 32766 has not been created yet -- apparent
wraparound in 2 instances.

1. On one database server during the nightly task that does the "vacuum
analyze". I upgraded to postgres 9.4.9 as there is a reference to a bug
fixed with a reference to this error but I am still getting the same
error when I vacuum analyze. This database was created using pg_dump and
pg_restore (not pg_upgrade) from the previous installation which was
still postgres 9.4.6 but on SLES instead of CentOS. I only have one
postgres version installed on the server.

Autovacuum is turned on and I also have a task that runs vacuum analyze
every night.

postgresql94-9.4.9-1PGDG.rhel7.x86_64
postgresql94-plperl-9.4.9-1PGDG.rhel7.x86_64
postgresql94-libs-9.4.9-1PGDG.rhel7.x86_64
postgresql94-server-9.4.9-1PGDG.rhel7.x86_64
postgresql94-contrib-9.4.9-1PGDG.rhel7.x86_64

psql (9.4.9)
=# vacuum analyze;
ERROR:  MultiXactId 32766 has not been created yet -- apparent wraparound

This server only runs since June 2016.

2. The second instance of this error is on a new database server during
pg_restore on few CREATE INDEX, PRIMARY KEYS and FOREIGN KEYS statements
(postgres 9.4.8 on centos 7). In this case I dropped and recreated the
database and the second try succeeded without errors.

postgresql-14.csv:2016-08-14 05:33:59.753
CST,"postgres","test",19555,"[local]",57b055d4.4c63,11,"CREATE
INDEX",2016-08-14 05:28:20 CST,1/27230,5381,ERROR,XX000,"MultiXactId
1667854355 has not been created yet -- apparent wraparound",,"CREATE
INDEX...;


Please let me know if I should provide more info.


Are these the same VM's as in your previous error(Corrupted Data) post?

Is the first error you mention on the db3 server from the previous 
error(Corrupted Data)?




Thank you in advance,
Ioana









--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Adrian Klaver]

On 08/15/2016 06:20 AM, Pete Fuller wrote:

Hello all,

We are attempting to upgrade a production 9.2 postgres cluster to 9.5. The 
server is running Centos 7 with the Centos version - currently 9.2.15 We are 
installing the postgresql provided rpm of postgresql9.5 from the postgresql 
repo, currently 9.5.4. In testing and on our development cluster, the upgrade 
worked without issue but something is happening on our production DB that we 
can not figure out.

The 9.2 cluster is in streaming replication controlled by pacemaker. Steps we 
have been using are the following.

• verify the replica server is in a good state with up to date data, 
then put the machine in standby, stopping the 9.2 db
• Removing the recovery.conf file and starting the 9.2 server manually 
to get some stats to verify later (counts of rows from several key tables)
• Stop the 9.2 server, (/bin/pg_ctl stop -D /var/lib/pgsql/data ) and 
install the 9.5 binaries.
• init the 9.5 db
• run a pg_upgrade with the link option. The full command we are using 
is
/usr/pgsql-9.5/bin/pg_upgrade -d /var/lib/pgsql/data -D /var/lib/pgsql/9.5/data 
-b /bin -B /usr/pgsql-9.5/bin/ --link -r -v


Are you sure you are linking the appropriate directories?

Are there any tablespaces in the mix?


• run the full vacuum analyze script that pg_upgrade generated

What we are finding in testing is we get thru the upgrade process, start the 
9.5 server manually to compare the row counts we collected immediately before 
the upgrade and we are seeing different data, sometimes drastically. This data 
includes missing user accounts, etc, that is not perishable data.

For example - 9.2 standalone before running pg_upgrade gives this information
tracks=# select count(*) from users;
 count
---
 13945
(1 row)

tracks=# select count(*) from sessions;
 count
---
   559
(1 row)

PG 9.5 after the upgrade - same machine, upgraded database

tracks=# select count(*) from sessions;
---
   155
(1 row)

tracks=# select count(*) from users;
 count
---
 13157
(1 row)


We have enabled verbose logging and have reviewed it but are seeing no errors 
in the migration. The 9.5 server starts without complaint and if we did not do 
these manual checks we would not see a problem. We have attempted this upgrade 
on this machine repeatedly in prepping for our maintenance windows, and on 
occasion our manual checks have shown good numbers.  At this point, though, it 
has failed silently enough times that I have little confidence in the data even 
if our rudimentary checks do show identical counts after the upgrade.

Has anyone else seen this issue?

The only 'hack' during the upgrade that isn't straight from the 
postgres_upgrade man page is to handle a renamed option in postgresql that 
cents back ported - unix_socket_directories. Using the rename of pg_ctl that is 
referenced in the top answer here to force a couple variables into the centos 
provided pg_ctl binary - 
http://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configuration-parameter-unix-socket-directory


Any information or suggestions would be helpful at this point.









--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ERROR: MultiXactId XXXXX has not been created yet -- apparent wraparound

[Ioana Danes]

Hello Adrian,

On Mon, Aug 15, 2016 at 10:00 AM, Adrian Klaver 
wrote:

> On 08/15/2016 06:48 AM, Ioana Danes wrote:
>
>> Hello All,
>>
>> I am running postgres 9.4.8 on centos 7 and few days ago I started to
>> get this error MultiXactId 32766 has not been created yet -- apparent
>> wraparound in 2 instances.
>>
>> 1. On one database server during the nightly task that does the "vacuum
>> analyze". I upgraded to postgres 9.4.9 as there is a reference to a bug
>> fixed with a reference to this error but I am still getting the same
>> error when I vacuum analyze. This database was created using pg_dump and
>> pg_restore (not pg_upgrade) from the previous installation which was
>> still postgres 9.4.6 but on SLES instead of CentOS. I only have one
>> postgres version installed on the server.
>>
>> Autovacuum is turned on and I also have a task that runs vacuum analyze
>> every night.
>>
>> postgresql94-9.4.9-1PGDG.rhel7.x86_64
>> postgresql94-plperl-9.4.9-1PGDG.rhel7.x86_64
>> postgresql94-libs-9.4.9-1PGDG.rhel7.x86_64
>> postgresql94-server-9.4.9-1PGDG.rhel7.x86_64
>> postgresql94-contrib-9.4.9-1PGDG.rhel7.x86_64
>>
>> psql (9.4.9)
>> =# vacuum analyze;
>> ERROR:  MultiXactId 32766 has not been created yet -- apparent wraparound
>>
>> This server only runs since June 2016.
>>
>> 2. The second instance of this error is on a new database server during
>> pg_restore on few CREATE INDEX, PRIMARY KEYS and FOREIGN KEYS statements
>> (postgres 9.4.8 on centos 7). In this case I dropped and recreated the
>> database and the second try succeeded without errors.
>>
>> postgresql-14.csv:2016-08-14 05:33:59.753
>> CST,"postgres","test",19555,"[local]",57b055d4.4c63,11,"CREATE
>> INDEX",2016-08-14 05:28:20 CST,1/27230,5381,ERROR,XX000,"MultiXactId
>> 1667854355 has not been created yet -- apparent wraparound",,"CREATE
>> INDEX...;
>>
>>
>> Please let me know if I should provide more info.
>>
>
> Are these the same VM's as in your previous error(Corrupted Data) post?
>
> Is the first error you mention on the db3 server from the previous
> error(Corrupted Data)?
>
>
They are not the same servers but they have similar setup. Fortunately they
are qa and development.

On the same servers (qa) I also started to get some corruption messages on
pg_dump.

2016-08-15 00:04:17.238 CST,"postgres","abrazo",7600,"
[local]",57b15a62.1db0,6,"COPY",2016-08-15 00:00:02
CST,13/6895726,0,ERROR,XX000,"compressed data is corrupt",,"COPY ... TO
stdout;",,,"pg_dump"

Also yesterday I dropped the table that had the problem in the previous
post and restored it from db1 (the good database) and all seemed good but
few hours after I fixed that table, db4 that is a the PITR slave of db3
started in production with a checksum error...

All the problems started to appear when we switched to CentOS with kvm. I
have instances on postgres 9.4 in production and qa on SLES with XEN since
few years ago and never had any kind of issues, and that would be around 70
database servers. We also run on postgres since 12 years ago without major
problems...


Thank you very much for your help,
ioana


>
>> Thank you in advance,
>> Ioana
>>
>>
>>
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Pete Fuller]

Directories are correct.  We do not utilize tablespaces. 




> On Aug 15, 2016, at 10:06 AM, Adrian Klaver  wrote:
> 
> On 08/15/2016 06:20 AM, Pete Fuller wrote:
>> Hello all,
>> 
>> We are attempting to upgrade a production 9.2 postgres cluster to 9.5. The 
>> server is running Centos 7 with the Centos version - currently 9.2.15 We are 
>> installing the postgresql provided rpm of postgresql9.5 from the postgresql 
>> repo, currently 9.5.4. In testing and on our development cluster, the 
>> upgrade worked without issue but something is happening on our production DB 
>> that we can not figure out.
>> 
>> The 9.2 cluster is in streaming replication controlled by pacemaker. Steps 
>> we have been using are the following.
>> 
>>  • verify the replica server is in a good state with up to date data, 
>> then put the machine in standby, stopping the 9.2 db
>>  • Removing the recovery.conf file and starting the 9.2 server manually 
>> to get some stats to verify later (counts of rows from several key tables)
>>  • Stop the 9.2 server, (/bin/pg_ctl stop -D /var/lib/pgsql/data ) and 
>> install the 9.5 binaries.
>>  • init the 9.5 db
>>  • run a pg_upgrade with the link option. The full command we are using 
>> is
>> /usr/pgsql-9.5/bin/pg_upgrade -d /var/lib/pgsql/data -D 
>> /var/lib/pgsql/9.5/data -b /bin -B /usr/pgsql-9.5/bin/ --link -r -v
> 
> Are you sure you are linking the appropriate directories?
> 
> Are there any tablespaces in the mix?
> 
>>  • run the full vacuum analyze script that pg_upgrade generated
>> 
>> What we are finding in testing is we get thru the upgrade process, start the 
>> 9.5 server manually to compare the row counts we collected immediately 
>> before the upgrade and we are seeing different data, sometimes drastically. 
>> This data includes missing user accounts, etc, that is not perishable data.
>> 
>> For example - 9.2 standalone before running pg_upgrade gives this information
>> tracks=# select count(*) from users;
>> count
>> ---
>> 13945
>> (1 row)
>> 
>> tracks=# select count(*) from sessions;
>> count
>> ---
>>   559
>> (1 row)
>> 
>> PG 9.5 after the upgrade - same machine, upgraded database
>> 
>> tracks=# select count(*) from sessions;
>> ---
>>   155
>> (1 row)
>> 
>> tracks=# select count(*) from users;
>> count
>> ---
>> 13157
>> (1 row)
>> 
>> 
>> We have enabled verbose logging and have reviewed it but are seeing no 
>> errors in the migration. The 9.5 server starts without complaint and if we 
>> did not do these manual checks we would not see a problem. We have attempted 
>> this upgrade on this machine repeatedly in prepping for our maintenance 
>> windows, and on occasion our manual checks have shown good numbers.  At this 
>> point, though, it has failed silently enough times that I have little 
>> confidence in the data even if our rudimentary checks do show identical 
>> counts after the upgrade.
>> 
>> Has anyone else seen this issue?
>> 
>> The only 'hack' during the upgrade that isn't straight from the 
>> postgres_upgrade man page is to handle a renamed option in postgresql that 
>> cents back ported - unix_socket_directories. Using the rename of pg_ctl that 
>> is referenced in the top answer here to force a couple variables into the 
>> centos provided pg_ctl binary - 
>> http://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configuration-parameter-unix-socket-directory
>> 
>> 
>> Any information or suggestions would be helpful at this point.
>> 
>> 
>> 
>> 
>> 
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 

Re: [GENERAL] ERROR: MultiXactId XXXXX has not been created yet -- apparent wraparound

[Adrian Klaver]

On 08/15/2016 07:40 AM, Ioana Danes wrote:

Hello Adrian,

On Mon, Aug 15, 2016 at 10:00 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 08/15/2016 06:48 AM, Ioana Danes wrote:

Hello All,

I am running postgres 9.4.8 on centos 7 and few days ago I
started to
get this error MultiXactId 32766 has not been created yet --
apparent
wraparound in 2 instances.

1. On one database server during the nightly task that does the
"vacuum
analyze". I upgraded to postgres 9.4.9 as there is a reference
to a bug
fixed with a reference to this error but I am still getting the same
error when I vacuum analyze. This database was created using
pg_dump and
pg_restore (not pg_upgrade) from the previous installation which was
still postgres 9.4.6 but on SLES instead of CentOS. I only have one
postgres version installed on the server.

Autovacuum is turned on and I also have a task that runs vacuum
analyze
every night.

postgresql94-9.4.9-1PGDG.rhel7.x86_64
postgresql94-plperl-9.4.9-1PGDG.rhel7.x86_64
postgresql94-libs-9.4.9-1PGDG.rhel7.x86_64
postgresql94-server-9.4.9-1PGDG.rhel7.x86_64
postgresql94-contrib-9.4.9-1PGDG.rhel7.x86_64

psql (9.4.9)
=# vacuum analyze;
ERROR:  MultiXactId 32766 has not been created yet -- apparent
wraparound

This server only runs since June 2016.

2. The second instance of this error is on a new database server
during
pg_restore on few CREATE INDEX, PRIMARY KEYS and FOREIGN KEYS
statements
(postgres 9.4.8 on centos 7). In this case I dropped and
recreated the
database and the second try succeeded without errors.

postgresql-14.csv:2016-08-14 05:33:59.753
CST,"postgres","test",19555,"[local]",57b055d4.4c63,11,"CREATE
INDEX",2016-08-14 05:28:20 CST,1/27230,5381,ERROR,XX000,"MultiXactId
1667854355 has not been created yet -- apparent
wraparound",,"CREATE
INDEX...;


Please let me know if I should provide more info.


Are these the same VM's as in your previous error(Corrupted Data) post?

Is the first error you mention on the db3 server from the previous
error(Corrupted Data)?


They are not the same servers but they have similar setup. Fortunately
they are qa and development.


Should have asked previously, are they on the same host machine?

Basically, something is corrupting data, just trying to narrow it down 
to hardware or software. Looking to figure out if the problem(s) follow 
the physical machine or the software the Postgres is running on.




On the same servers (qa) I also started to get some corruption messages
on pg_dump.

2016-08-15 00:04:17.238
CST,"postgres","abrazo",7600,"[local]",57b15a62.1db0,6,"COPY",2016-08-15
00:00:02 CST,13/6895726,0,ERROR,XX000,"compressed data is
corrupt",,"COPY ... TO stdout;",,,"pg_dump"

Also yesterday I dropped the table that had the problem in the previous
post and restored it from db1 (the good database) and all seemed good
but few hours after I fixed that table, db4 that is a the PITR slave of
db3 started in production with a checksum error...

All the problems started to appear when we switched to CentOS with kvm.


On the same machines or new/different machines?


I have instances on postgres 9.4 in production and qa on SLES with XEN
since few years ago and never had any kind of issues, and that would be
around 70 database servers. We also run on postgres since 12 years ago
without major problems...


Thank you very much for your help,
ioana



Thank you in advance,
Ioana








--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Adrian Klaver]

On 08/15/2016 07:40 AM, Pete Fuller wrote:

Directories are correct.  We do not utilize tablespaces.


Anything obviously different in the setup between your production 
servers and the testing and development clusters?








On Aug 15, 2016, at 10:06 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:

On 08/15/2016 06:20 AM, Pete Fuller wrote:

Hello all,

We are attempting to upgrade a production 9.2 postgres cluster to
9.5. The server is running Centos 7 with the Centos version -
currently 9.2.15 We are installing the postgresql provided rpm of
postgresql9.5 from the postgresql repo, currently 9.5.4. In testing
and on our development cluster, the upgrade worked without issue but
something is happening on our production DB that we can not figure out.

The 9.2 cluster is in streaming replication controlled by pacemaker.
Steps we have been using are the following.

• verify the replica server is in a good state with up to date data,
then put the machine in standby, stopping the 9.2 db
• Removing the recovery.conf file and starting the 9.2 server
manually to get some stats to verify later (counts of rows from
several key tables)
• Stop the 9.2 server, (/bin/pg_ctl stop -D /var/lib/pgsql/data ) and
install the 9.5 binaries.
• init the 9.5 db
• run a pg_upgrade with the link option. The full command we are using is
/usr/pgsql-9.5/bin/pg_upgrade -d /var/lib/pgsql/data -D
/var/lib/pgsql/9.5/data -b /bin -B /usr/pgsql-9.5/bin/ --link -r -v


Are you sure you are linking the appropriate directories?

Are there any tablespaces in the mix?


• run the full vacuum analyze script that pg_upgrade generated

What we are finding in testing is we get thru the upgrade process,
start the 9.5 server manually to compare the row counts we collected
immediately before the upgrade and we are seeing different data,
sometimes drastically. This data includes missing user accounts, etc,
that is not perishable data.

For example - 9.2 standalone before running pg_upgrade gives this
information
tracks=# select count(*) from users;
count
---
13945
(1 row)

tracks=# select count(*) from sessions;
count
---
  559
(1 row)

PG 9.5 after the upgrade - same machine, upgraded database

tracks=# select count(*) from sessions;
---
  155
(1 row)

tracks=# select count(*) from users;
count
---
13157
(1 row)


We have enabled verbose logging and have reviewed it but are seeing
no errors in the migration. The 9.5 server starts without complaint
and if we did not do these manual checks we would not see a problem.
We have attempted this upgrade on this machine repeatedly in prepping
for our maintenance windows, and on occasion our manual checks have
shown good numbers.  At this point, though, it has failed silently
enough times that I have little confidence in the data even if our
rudimentary checks do show identical counts after the upgrade.

Has anyone else seen this issue?

The only 'hack' during the upgrade that isn't straight from the
postgres_upgrade man page is to handle a renamed option in postgresql
that cents back ported - unix_socket_directories. Using the rename of
pg_ctl that is referenced in the top answer here to force a couple
variables into the centos provided pg_ctl binary -
http://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configuration-parameter-unix-socket-directory


Any information or suggestions would be helpful at this point.









--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Pete Fuller]

We have not found any obvious differences, other than the size of the databases 
(~ 100 gig on the testing site vs 400 Gig in production).  In fact, the upgrade 
path seems to work with our production db when testing on our offsite replica 
that lives in our backup datacenter, near identical hardware and setup with 
same install scripts. 



> On Aug 15, 2016, at 10:55 AM, Adrian Klaver  wrote:
> 
> On 08/15/2016 07:40 AM, Pete Fuller wrote:
>> Directories are correct.  We do not utilize tablespaces.
> 
> Anything obviously different in the setup between your production servers and 
> the testing and development clusters?
> 
>> 
>> 
>> 
>> 
>>> On Aug 15, 2016, at 10:06 AM, Adrian Klaver >> 
>>> >> 
>>> wrote:
>>> 
>>> On 08/15/2016 06:20 AM, Pete Fuller wrote:
 Hello all,
 
 We are attempting to upgrade a production 9.2 postgres cluster to
 9.5. The server is running Centos 7 with the Centos version -
 currently 9.2.15 We are installing the postgresql provided rpm of
 postgresql9.5 from the postgresql repo, currently 9.5.4. In testing
 and on our development cluster, the upgrade worked without issue but
 something is happening on our production DB that we can not figure out.
 
 The 9.2 cluster is in streaming replication controlled by pacemaker.
 Steps we have been using are the following.
 
 • verify the replica server is in a good state with up to date data,
 then put the machine in standby, stopping the 9.2 db
 • Removing the recovery.conf file and starting the 9.2 server
 manually to get some stats to verify later (counts of rows from
 several key tables)
 • Stop the 9.2 server, (/bin/pg_ctl stop -D /var/lib/pgsql/data ) and
 install the 9.5 binaries.
 • init the 9.5 db
 • run a pg_upgrade with the link option. The full command we are using is
 /usr/pgsql-9.5/bin/pg_upgrade -d /var/lib/pgsql/data -D
 /var/lib/pgsql/9.5/data -b /bin -B /usr/pgsql-9.5/bin/ --link -r -v
>>> 
>>> Are you sure you are linking the appropriate directories?
>>> 
>>> Are there any tablespaces in the mix?
>>> 
 • run the full vacuum analyze script that pg_upgrade generated
 
 What we are finding in testing is we get thru the upgrade process,
 start the 9.5 server manually to compare the row counts we collected
 immediately before the upgrade and we are seeing different data,
 sometimes drastically. This data includes missing user accounts, etc,
 that is not perishable data.
 
 For example - 9.2 standalone before running pg_upgrade gives this
 information
 tracks=# select count(*) from users;
 count
 ---
 13945
 (1 row)
 
 tracks=# select count(*) from sessions;
 count
 ---
  559
 (1 row)
 
 PG 9.5 after the upgrade - same machine, upgraded database
 
 tracks=# select count(*) from sessions;
 ---
  155
 (1 row)
 
 tracks=# select count(*) from users;
 count
 ---
 13157
 (1 row)
 
 
 We have enabled verbose logging and have reviewed it but are seeing
 no errors in the migration. The 9.5 server starts without complaint
 and if we did not do these manual checks we would not see a problem.
 We have attempted this upgrade on this machine repeatedly in prepping
 for our maintenance windows, and on occasion our manual checks have
 shown good numbers.  At this point, though, it has failed silently
 enough times that I have little confidence in the data even if our
 rudimentary checks do show identical counts after the upgrade.
 
 Has anyone else seen this issue?
 
 The only 'hack' during the upgrade that isn't straight from the
 postgres_upgrade man page is to handle a renamed option in postgresql
 that cents back ported - unix_socket_directories. Using the rename of
 pg_ctl that is referenced in the top answer here to force a couple
 variables into the centos provided pg_ctl binary -
 http://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configuration-parameter-unix-socket-directory
 
 
 Any information or suggestions would be helpful at this point.
 
 
 
 
 
 
>>> 
>>> 
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com  
>>> >
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 

Re: [GENERAL] ERROR: MultiXactId XXXXX has not been created yet -- apparent wraparound

[Ioana Danes]

On Mon, Aug 15, 2016 at 10:52 AM, Adrian Klaver 
wrote:

> On 08/15/2016 07:40 AM, Ioana Danes wrote:
>
>> Hello Adrian,
>>
>> On Mon, Aug 15, 2016 at 10:00 AM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 08/15/2016 06:48 AM, Ioana Danes wrote:
>>
>> Hello All,
>>
>> I am running postgres 9.4.8 on centos 7 and few days ago I
>> started to
>> get this error MultiXactId 32766 has not been created yet --
>> apparent
>> wraparound in 2 instances.
>>
>> 1. On one database server during the nightly task that does the
>> "vacuum
>> analyze". I upgraded to postgres 9.4.9 as there is a reference
>> to a bug
>> fixed with a reference to this error but I am still getting the
>> same
>> error when I vacuum analyze. This database was created using
>> pg_dump and
>> pg_restore (not pg_upgrade) from the previous installation which
>> was
>> still postgres 9.4.6 but on SLES instead of CentOS. I only have
>> one
>> postgres version installed on the server.
>>
>> Autovacuum is turned on and I also have a task that runs vacuum
>> analyze
>> every night.
>>
>> postgresql94-9.4.9-1PGDG.rhel7.x86_64
>> postgresql94-plperl-9.4.9-1PGDG.rhel7.x86_64
>> postgresql94-libs-9.4.9-1PGDG.rhel7.x86_64
>> postgresql94-server-9.4.9-1PGDG.rhel7.x86_64
>> postgresql94-contrib-9.4.9-1PGDG.rhel7.x86_64
>>
>> psql (9.4.9)
>> =# vacuum analyze;
>> ERROR:  MultiXactId 32766 has not been created yet -- apparent
>> wraparound
>>
>> This server only runs since June 2016.
>>
>> 2. The second instance of this error is on a new database server
>> during
>> pg_restore on few CREATE INDEX, PRIMARY KEYS and FOREIGN KEYS
>> statements
>> (postgres 9.4.8 on centos 7). In this case I dropped and
>> recreated the
>> database and the second try succeeded without errors.
>>
>> postgresql-14.csv:2016-08-14 05:33:59.753
>> CST,"postgres","test",19555,"[local]",57b055d4.4c63,11,"CREATE
>> INDEX",2016-08-14 05:28:20 CST,1/27230,5381,ERROR,XX000,"
>> MultiXactId
>> 1667854355 has not been created yet -- apparent
>> wraparound",,"CREATE
>> INDEX...;
>>
>>
>> Please let me know if I should provide more info.
>>
>>
>> Are these the same VM's as in your previous error(Corrupted Data)
>> post?
>>
>> Is the first error you mention on the db3 server from the previous
>> error(Corrupted Data)?
>>
>>
>> They are not the same servers but they have similar setup. Fortunately
>> they are qa and development.
>>
>
> Should have asked previously, are they on the same host machine?
>
> No they are on different physical machines.


> Basically, something is corrupting data, just trying to narrow it down to
> hardware or software. Looking to figure out if the problem(s) follow the
> physical machine or the software the Postgres is running on.
>
I would say software because on the same physical machines we had SLES +
XEN running for years without any kind of corruption.

>
>
>> On the same servers (qa) I also started to get some corruption messages
>> on pg_dump.
>>
>> 2016-08-15 00:04:17.238
>> CST,"postgres","abrazo",7600,"[local]",57b15a62.1db0,6,"COPY",2016-08-15
>> 00:00:02 CST,13/6895726,0,ERROR,XX000,"compressed data is
>> corrupt",,"COPY ... TO stdout;",,,"pg_dump"
>>
>> Also yesterday I dropped the table that had the problem in the previous
>> post and restored it from db1 (the good database) and all seemed good
>> but few hours after I fixed that table, db4 that is a the PITR slave of
>> db3 started in production with a checksum error...
>>
>> All the problems started to appear when we switched to CentOS with kvm.
>>
>
> On the same machines or new/different machines?
>
> I have instances on postgres 9.4 in production and qa on SLES with XEN
>> since few years ago and never had any kind of issues, and that would be
>> around 70 database servers. We also run on postgres since 12 years ago
>> without major problems...
>>
>>
>> Thank you very much for your help,
>> ioana
>>
>>
>>
>> Thank you in advance,
>> Ioana
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] ERROR: MultiXactId XXXXX has not been created yet -- apparent wraparound

[Adrian Klaver]

On 08/15/2016 08:05 AM, Ioana Danes wrote:



On Mon, Aug 15, 2016 at 10:52 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:




They are not the same servers but they have similar setup.
Fortunately
they are qa and development.


Should have asked previously, are they on the same host machine?

No they are on different physical machines.


Basically, something is corrupting data, just trying to narrow it
down to hardware or software. Looking to figure out if the
problem(s) follow the physical machine or the software the Postgres
is running on.

I would say software because on the same physical machines we had SLES +
XEN running for years without any kind of corruption.



I would tend to agree, but one cannot discount the effects of age. Could 
be that the OS switch happened just prior to some age induced failure in 
hardware.


Still, do you have a running Postgres instance or have you tried running 
a Postgres instance on CentOS that is not in a VM?



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Adrian Klaver]

On 08/15/2016 08:04 AM, Pete Fuller wrote:

We have not found any obvious differences, other than the size of the
databases (~ 100 gig on the testing site vs 400 Gig in production).  In
fact, the upgrade path seems to work with our production db when testing
on our offsite replica that lives in our backup datacenter, near
identical hardware and setup with same install scripts.



Hmm, tried another cup of coffee, that did not work, I got nothing at 
the moment:(


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Critical failure of standby

[Jeff Janes]

On Thu, Aug 11, 2016 at 10:39 PM, James Sewell 
wrote:

> Hello,
>
> We recently experienced a critical failure when failing to a DR
> environment.
>
> This is in the following environment:
>
>
>- 3 x PostgreSQL machines in Prod in a sync replication cluster
>- 3 x PostgreSQL machines in DR, with a single machine async and the
>other two cascading from the first machine.
>
> There was network failure which isolated Production from everything else,
> Production has no errors during this time (and has now come back OK).
>
> DR did not tolerate the break, the following appeared in the logs and none
> of them can start postgres. There were no queries coming into DR at the
> time of the break.
>
> Please note that the "Host Key verification failed" messages are due to
> the scp command not functioning. This means restore_command is not working
> to restore from the XLOG archive, but should not effect anything else.
>


In my experience, PostgreSQL issues its own error messages when
restore_command fails.  So I see both the error from the command itself,
and an error from PostgreSQL.  Why don't you see that?  Is the
restore_command failing, but then reporting that it succeeded?

And if you can't get files from the XLOG archive, why do you think that
that is OK?

Cheers,

Jeff

Re: [GENERAL] RowExclusiveLock timeout while autovacuum

[Jeff Janes]

On Sun, Aug 14, 2016 at 9:33 PM, zh1029  wrote:
> Hi,
>   We are using PostgreSQL 9.3.11. We are observing DB update failed due to
> lock timeout. failure because waiting for RowExclusiveLock.  Autovacuum uses
> plain vacuum which uses ShareUpdateExclusiveLock. right?
> But from Postgres Manual chapter 13.3. Explicit Locking. both Locks has no
> conflict. So in which situation conflict lock happened. Does autovacuum use
> other lock than ShareUpdateExclusiveLock in certain situation?
>
> Aug 10 15:03:16 DB-1 postgres[3314]: [5-1] DEBUG:  sending cancel to
> blocking autovacuum PID 25047

What is your log_line_prefix?  I am not familiar with the '[5-1]'
thing.  I assume %l is the final part, but what is the first first
part?  Also, including the milliseconds in the timestamps can help a
lot (%m rather than %t), because a 200ms gap between log messages can
lead to much different interpretation than a 2ms gap.  And is 3314 the
port you are running on, or is it the pid of the process?  Is DEBUG
set globally or locally?

Why don't we ever see 25047 responding to the cancellation?

Anyway, ShareUpdateExclusiveLock and RowExclusiveLock do not directly
interfere with each other.  If you have a chain of processes blocking
each other, it is possible that one blocks the other indirectly.  Your
log file seems incomplete, so it is hard to say what is going on.
Does your app use any explicit table locks?

Cheers,

Jeff


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[rob stone]

Hello,
On Mon, 2016-08-15 at 08:45 -0700, Adrian Klaver wrote:
> On 08/15/2016 08:04 AM, Pete Fuller wrote:
> > 
> > We have not found any obvious differences, other than the size of
> > the
> > databases (~ 100 gig on the testing site vs 400 Gig in
> > production).  In
> > fact, the upgrade path seems to work with our production db when
> > testing
> > on our offsite replica that lives in our backup datacenter, near
> > identical hardware and setup with same install scripts.
> > 
> 
> Hmm, tried another cup of coffee, that did not work, I got nothing
> at 
> the moment:(
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com
> 
> 


Assuming you have enough spare disk capacity, have you not thought of
going from 9.2 to 9.3 and from there to 9.4, etc., instead of doing it
in one giant leap?

If the upgrade to 9.3 does not cause any hassles, you could at least
put that into production and then test out the 9.3 to 9.4 upgrade, and
so on.

Just my two cents worth.

Cheers,
rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Adrian Klaver]

On 08/15/2016 08:04 AM, Pete Fuller wrote:

We have not found any obvious differences, other than the size of the
databases (~ 100 gig on the testing site vs 400 Gig in production).  In
fact, the upgrade path seems to work with our production db when testing
on our offsite replica that lives in our backup datacenter, near
identical hardware and setup with same install scripts.



Idea that popped into head, how do you get the data to the dbs in the 
offsite replica versus how do you get the data to the live replica?


Also are all the machines running the same chip architecture?



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Pete Fuller]

Same chips, same model Dell Poweredge actually.  The process is pretty much the 
same between the local and offsite replicas, we have a home-brew script that 
runs an rsync from the master to the replica.  On the local replica, it then 
takes the machine out of pacemaker standby, on the offsite replica it starts up 
postgres directly.  

One thought we had today.  There is a chance some local crons and some 
monitoring scripts might be attempting to run queries on  the DB during the 
migration.  Is there any chance that could foul something up?  its the one 
difference we could think of after a brainstorming session here.  We are going 
to attempt another test this evening with all crons disabled and monitoring 
disabled. 




Pete Fuller, Tracking Server Systems Administrator
2055 North Brown Road, Suite 225, Lawrenceville, GA 30043
M: 678.677.5864 
pete_ful...@3sisecurity.com  │ 3sisecurity.com 

Innovation That Protects

> On Aug 15, 2016, at 1:54 PM, Adrian Klaver  wrote:
> 
> On 08/15/2016 08:04 AM, Pete Fuller wrote:
>> We have not found any obvious differences, other than the size of the
>> databases (~ 100 gig on the testing site vs 400 Gig in production).  In
>> fact, the upgrade path seems to work with our production db when testing
>> on our offsite replica that lives in our backup datacenter, near
>> identical hardware and setup with same install scripts.
>> 
> 
> Idea that popped into head, how do you get the data to the dbs in the offsite 
> replica versus how do you get the data to the live replica?
> 
> Also are all the machines running the same chip architecture?
> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Adrian Klaver]

On 08/15/2016 11:52 AM, Pete Fuller wrote:

Same chips, same model Dell Poweredge actually.  The process is pretty
much the same between the local and offsite replicas, we have a
home-brew script that runs an rsync from the master to the replica.  On
the local replica, it then takes the machine out of pacemaker standby,
on the offsite replica it starts up postgres directly.

One thought we had today.  There is a chance some local crons and some
monitoring scripts might be attempting to run queries on  the DB during
the migration.  Is there any chance that could foul something up?  its
the one difference we could think of after a brainstorming session here.
 We are going to attempt another test this evening with all crons
disabled and monitoring disabled.


https://www.postgresql.org/docs/9.5/static/pgupgrade.html

"Obviously, no one should be accessing the clusters during the upgrade. 
pg_upgrade defaults to running servers on port 50432 to avoid unintended 
client connections. You can use the same port number for both clusters 
when doing an upgrade because the old and new clusters will not be 
running at the same time. However, when checking an old running server, 
the old and new port numbers must be different."


In your OP you do not show overriding pg_upgrade defaults for ports, so 
assuming the scripts are looking for the live ports and not the upgrade 
ports that should not be an issue, with the caveat below.


There is is windows of opportunity here(again from your OP):

"
• verify the replica server is in a good state with up to date data, 
then put the machine in standby, stopping the 9.2 db
• Removing the recovery.conf file and starting the 9.2 server manually 
to get some stats to verify later (counts of rows from several key tables)
• Stop the 9.2 server, (/bin/pg_ctl stop -D /var/lib/pgsql/data ) and 
install the 9.5 binaries.

"





Pete Fuller, Tracking Server Systems Administrator
2055 North Brown Road, Suite 225, Lawrenceville, GA 30043
M: 678.677.5864
pete_ful...@3sisecurity.com 
│ 3sisecurity.com 

/Innovation That Protects/


On Aug 15, 2016, at 1:54 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:

On 08/15/2016 08:04 AM, Pete Fuller wrote:

We have not found any obvious differences, other than the size of the
databases (~ 100 gig on the testing site vs 400 Gig in production).  In
fact, the upgrade path seems to work with our production db when testing
on our offsite replica that lives in our backup datacenter, near
identical hardware and setup with same install scripts.



Idea that popped into head, how do you get the data to the dbs in the
offsite replica versus how do you get the data to the live replica?

Also are all the machines running the same chip architecture?



--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Bruce Momjian]

On Mon, Aug 15, 2016 at 12:18:04PM -0700, Adrian Klaver wrote:
> https://www.postgresql.org/docs/9.5/static/pgupgrade.html
> 
> "Obviously, no one should be accessing the clusters during the upgrade.
> pg_upgrade defaults to running servers on port 50432 to avoid unintended
> client connections. You can use the same port number for both clusters when
> doing an upgrade because the old and new clusters will not be running at the
> same time. However, when checking an old running server, the old and new
> port numbers must be different."
> 
> In your OP you do not show overriding pg_upgrade defaults for ports, so
> assuming the scripts are looking for the live ports and not the upgrade
> ports that should not be an issue.

Agreed.  I have no idea what would cause this, and have never heard a
report like this before.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Looking for software to 'enqueue' connections

[Edmundo Robles]

Hi!
I want find  a software to 'enqueue' the client connections to database, so
if i reach the max limit the query must be holding in a queue   until one
connection is released.

I have  many devices (100+) saving  their state to a database,  each
minute,  but  the table is too large more than 13,000,000 of records and
many indexes, so, insert  one record takes 3 or more minutes.

Then,  there is a moment  at connection limit is reached :( and  lose
information

I tried with pgbouncer  to  'enqueue' the connections but  I  get  no
success, maybe   I missing something...

by the way:
I use postgres 9.4 with max_connections 100
and pgbouncer  max_connections to 100 and  reserve_pool_size=50

I hope you  can help me...

 thanks.

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

[Melvin Davidson]

On Mon, Aug 15, 2016 at 3:56 PM, Bruce Momjian  wrote:

> On Mon, Aug 15, 2016 at 12:18:04PM -0700, Adrian Klaver wrote:
> > https://www.postgresql.org/docs/9.5/static/pgupgrade.html
> >
> > "Obviously, no one should be accessing the clusters during the upgrade.
> > pg_upgrade defaults to running servers on port 50432 to avoid unintended
> > client connections. You can use the same port number for both clusters
> when
> > doing an upgrade because the old and new clusters will not be running at
> the
> > same time. However, when checking an old running server, the old and new
> > port numbers must be different."
> >
> > In your OP you do not show overriding pg_upgrade defaults for ports, so
> > assuming the scripts are looking for the live ports and not the upgrade
> > ports that should not be an issue.
>
> Agreed.  I have no idea what would cause this, and have never heard a
> report like this before.
>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Ancient Roman grave inscription +
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Just out of curiosity, have you you ANALYZE on you db after the upgrade but
before doing a count compare?

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Looking for software to 'enqueue' connections

[Adrian Klaver]

On 08/15/2016 01:30 PM, Edmundo Robles wrote:

Hi!
I want find  a software to 'enqueue' the client connections to database,
so if i reach the max limit the query must be holding in a queue   until
one connection is released.

I have  many devices (100+) saving  their state to a database,  each
minute,  but  the table is too large more than 13,000,000 of records and
many indexes, so, insert  one record takes 3 or more minutes.

Then,  there is a moment  at connection limit is reached :( and  lose
information

I tried with pgbouncer  to  'enqueue' the connections but  I  get  no
success, maybe   I missing something...

by the way:
I use postgres 9.4 with max_connections 100
and pgbouncer  max_connections to 100 and  reserve_pool_size=50

I hope you  can help me...


To really help it would be nice to know the hardware specifications you 
are working with:


CPU type and number.
RAM
Storage subsystem

Also some indication of what the load on you system as whole is. Cannot 
remember what your OS is, but information from something like top and 
iostat. The reasoning being that fooling with connections may not be of 
much help if the system is running at its max limits already. In other 
words it is possible a hardware upgrade is what is needed.




 thanks.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Looking for software to 'enqueue' connections

[John R Pierce]

On 8/15/2016 1:30 PM, Edmundo Robles wrote:
I want find  a software to 'enqueue' the client connections to 
database, so if i reach the max limit the query must be holding in a 
queue   until one connection is released.




pgbouncer is the correct answer, you may need to play about with the 
configuration a bit.   there's a few modes that might work, ideally, 
write your apps to connect to postgres, do a transaction, and 
disconnect, and limit the pool size so only so many connections can be 
active at a time.the other mode is to allow the clients to stay 
connected to the pool, but have a limited number of actual database 
connections that you allocate on a transaction basis.


I have  many devices (100+) saving  their state to a database,  each 
minute,  but  the table is too large more than 13,000,000 of records 
and many indexes, so, insert  one record takes 3 or more minutes.


that sounds terrible.   single row inserts shouldn't *ever* take 3 
minutes, if you have clients inserting a row a minute.   you may need 
faster disk storage, you may need to improve postgres tuning.


'many indexes' ?  how many ?  too many indexes would definitely slow 
inserts down.



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Looking for software to 'enqueue' connections

[Ilya Kazakevich]

Hello.

 

From:

http://www.pgpool.net/

pgpool-II also has a limit on the maximum number of connections, but extra 
connections will be queued instead of returning an error immediately.

 

But your configuration does not look optimal for me. Here are some things you 
may try:

1)  Get rid of indexes. Use this table as OLTP, then denormalize data and 
load it to OLAP table, build indecies and analyze it.

2)  Find bottleneck using your OS tools (is it I/O or CPU?) and improve 
appropriate subsystem)

3)  Use several servers (multimaster configuration like 
https://wiki.postgresql.org/wiki/Bucardo) 

 

Ilya Kazakevich

 

JetBrains

  http://www.jetbrains.com

The Drive to Develop

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Edmundo Robles
Sent: Monday, August 15, 2016 11:30 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Looking for software to 'enqueue' connections

 

Hi!  

I want find  a software to 'enqueue' the client connections to database, so if 
i reach the max limit the query must be holding in a queue   until one 
connection is released.

 

I have  many devices (100+) saving  their state to a database,  each minute,  
but  the table is too large more than 13,000,000 of records and many indexes, 
so, insert  one record takes 3 or more minutes.

 

Then,  there is a moment  at connection limit is reached :( and  lose 
information  

 

I tried with pgbouncer  to  'enqueue' the connections but  I  get  no success, 
maybe   I missing something...

 

by the way: 

I use postgres 9.4 with max_connections 100

and pgbouncer  max_connections to 100 and  reserve_pool_size=50

 

I hope you  can help me... 

 

 thanks.

 

Re: [GENERAL] Looking for software to 'enqueue' connections

[Edmundo Robles]

Adrian  i have hosted in a rackspace  a Debian 7  with 2G RAM.
John,   the table have 8 constraints and 5 indexes.
Ilya thanks for  the tip, i will search about OLTP.


On Mon, Aug 15, 2016 at 3:47 PM, Ilya Kazakevich <
ilya.kazakev...@jetbrains.com> wrote:

> Hello.
>
>
>
> From:
>
> http://www.pgpool.net/
>
> *pgpool-II* also has a limit on the maximum number of connections*, but
> extra connections will be queued instead of returning an error immediately.*
>
>
>
> But your configuration does not look optimal for me. Here are some things
> you may try:
>
> 1)  Get rid of indexes. Use this table as OLTP, then denormalize data
> and load it to OLAP table, build indecies and analyze it.
>
> 2)  Find bottleneck using your OS tools (is it I/O or CPU?) and
> improve appropriate subsystem)
>
> 3)  Use several servers (multimaster configuration like
> https://wiki.postgresql.org/wiki/Bucardo)
>
>
>
> Ilya Kazakevich
>
>
>
> JetBrains
>
> http://www.jetbrains.com
>
> The Drive to Develop
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-owner@
> postgresql.org] *On Behalf Of *Edmundo Robles
> *Sent:* Monday, August 15, 2016 11:30 PM
> *To:* pgsql-general@postgresql.org
> *Subject:* [GENERAL] Looking for software to 'enqueue' connections
>
>
>
> Hi!
>
> I want find  a software to 'enqueue' the client connections to database,
> so if i reach the max limit the query must be holding in a queue   until
> one connection is released.
>
>
>
> I have  many devices (100+) saving  their state to a database,  each
> minute,  but  the table is too large more than 13,000,000 of records and
> many indexes, so, insert  one record takes 3 or more minutes.
>
>
>
> Then,  there is a moment  at connection limit is reached :( and  lose
> information
>
>
>
> I tried with pgbouncer  to  'enqueue' the connections but  I  get  no
> success, maybe   I missing something...
>
>
>
> by the way:
>
> I use postgres 9.4 with max_connections 100
>
> and pgbouncer  max_connections to 100 and  reserve_pool_size=50
>
>
>
> I hope you  can help me...
>
>
>
>  thanks.
>
>
>

Re: [GENERAL] Looking for software to 'enqueue' connections

[Adrian Klaver]

On 08/15/2016 01:59 PM, Edmundo Robles wrote:

Please do not top post:
https://en.wikipedia.org/wiki/Posting_style

The preferred style is bottom or interleaved as it makes the thread 
easier to follow



Adrian  i have hosted in a rackspace  a Debian 7  with 2G RAM.


I assume that would be one of their virtual machines. The above is a 
start, but what would be helpful is actual system load data from the 
machine over time. As a start something on the order of:


aklaver@panda:~> uptime
 15:47pm  up   9:30,  3 users,  load average: 0.20, 0.35, 0.31

aklaver@panda:~> iostat  5
Linux 3.16.7-35-desktop (panda) 08/15/2016  _i686_  (3 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   4.790.033.041.520.00   90.62

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
sda  16.5451.80   379.131780484   13032770

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   2.690.003.230.070.00   94.01

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
sda   2.00 0.00   113.60  0568

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   0.740.002.845.670.00   90.75

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
sda  71.40 9.60  1786.40 48   8932


This is on my old desktop machine, so is not strictly representative of 
what you would see.


What we are looking for is a choke point. I am fairly certain that 
connections are not it and that your problem lies further upstream. 
Namely that your machine(virtual or otherwise) does not have the system 
resources(CPU, RAM, disk I/O) to keep up with the load you are placing 
on it. Until that is resolved anything you try to do downstream of the 
system resources is not going to solve the problem.




John,   the table have 8 constraints and 5 indexes.
Ilya thanks for  the tip, i will search about OLTP.






--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgbasebackup is failing after truncate

[Michael Paquier]

On Sat, Aug 13, 2016 at 12:41 AM, Jeff Janes  wrote:
> On Wed, Aug 10, 2016 at 11:06 PM, Yelai, Ramkumar
>  wrote:
>> At present, I have some requirement to truncate the data base  to reclaim
>> disk space and at the same time I need to take basebackup.
>>
>> Seems, truncate is successfully reclaimed the space but pgbasebackup failed
>> and reported the below error.
>>
>> “could not stat file or directory ./base/16384/25600: Permission denied.
>
> Are you on MS Windows?  If so, sounds like a possible variant of BUG #14243

That's really the same issue... See here for more details:
https://www.postgresql.org/message-id/20160712083220.1426.58...@wrigleys.postgresql.org
If you have ideas about making a difference between a real permission
error and STATUS_PENDING_DELETE, that would be great.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Critical failure of standby

[James Sewell]

Those are all good questions.

Essentially this is a situation where DR is network separated from Prod -
so I would expect the archive command to fail. I'll have to check the
script it must not be passing the error back through to PostgreSQL.

This still shouldn't cause database corruption though right? - it's just
not getting WALs.

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect



Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com  *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>

On Tue, Aug 16, 2016 at 2:09 AM, Jeff Janes  wrote:

> On Thu, Aug 11, 2016 at 10:39 PM, James Sewell 
> wrote:
>
>> Hello,
>>
>> We recently experienced a critical failure when failing to a DR
>> environment.
>>
>> This is in the following environment:
>>
>>
>>- 3 x PostgreSQL machines in Prod in a sync replication cluster
>>- 3 x PostgreSQL machines in DR, with a single machine async and the
>>other two cascading from the first machine.
>>
>> There was network failure which isolated Production from everything else,
>> Production has no errors during this time (and has now come back OK).
>>
>> DR did not tolerate the break, the following appeared in the logs and
>> none of them can start postgres. There were no queries coming into DR at
>> the time of the break.
>>
>> Please note that the "Host Key verification failed" messages are due to
>> the scp command not functioning. This means restore_command is not working
>> to restore from the XLOG archive, but should not effect anything else.
>>
>
>
> In my experience, PostgreSQL issues its own error messages when
> restore_command fails.  So I see both the error from the command itself,
> and an error from PostgreSQL.  Why don't you see that?  Is the
> restore_command failing, but then reporting that it succeeded?
>
> And if you can't get files from the XLOG archive, why do you think that
> that is OK?
>
> Cheers,
>
> Jeff
>

-- 

--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] Critical failure of standby

[John R Pierce]

On 8/15/2016 7:23 PM, James Sewell wrote:

Those are all good questions.

Essentially this is a situation where DR is network separated from 
Prod - so I would expect the archive command to fail. I'll have to 
check the script it must not be passing the error back through to 
PostgreSQL.


This still shouldn't cause database corruption though right? - it's 
just not getting WALs.


if the slave database is asking for the WAL's, it needs them. if it 
needs them and can't get them, then it can't catch up and start.




--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] C++ port of Postgres

[Adam Brusselback]

Just wondering what the end goal is for this project... Is it to just
maintain an up to date Postgres fork that will compile with a C++ compiler?
Is it to get a conversation going for a direction for Postgres itself to
move?  The former I don't see gaining much traction or doing all that much
for the state of the project. The latter possibly could if the community
gets on board.

Thanks,
-Adam

Re: [GENERAL] Critical failure of standby

[James Sewell]

Hey,

I understand that.

But a hot standby should always be ready to promote (given it originally
caught up) right?

I think it's a moot point really as some sort of corruption has been
introduced, the machines still won't wouldn't start after they could see
the archive destination again.

Cheers,

James Sewell,
Solutions Architect



Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com  *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>

On Tue, Aug 16, 2016 at 12:36 PM, John R Pierce  wrote:

> On 8/15/2016 7:23 PM, James Sewell wrote:
>
>> Those are all good questions.
>>
>> Essentially this is a situation where DR is network separated from Prod -
>> so I would expect the archive command to fail. I'll have to check the
>> script it must not be passing the error back through to PostgreSQL.
>>
>> This still shouldn't cause database corruption though right? - it's just
>> not getting WALs.
>>
>
> if the slave database is asking for the WAL's, it needs them. if it needs
> them and can't get them, then it can't catch up and start.
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 

--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] Critical failure of standby

[Sameer Kumar]

On Tue, Aug 16, 2016 at 1:10 PM James Sewell 
wrote:

> Hey,
>
> I understand that.
>
> But a hot standby should always be ready to promote (given it originally
> caught up) right?
>
> I think it's a moot point really as some sort of corruption has been
> introduced, the machines still won't wouldn't start after they could see
> the archive destination again.
>

Did you had a pending basebackup on the standby or a start backup (with no
matching stop backup)?

Was there a crash/immediate shutdown on the standby during this network
outage? Do you have full page writes/fsync disabled?


>
> Cheers,
>
> James Sewell,
> Solutions Architect
>
>
>
> Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
> *P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com
> *F *(+61) 2 8099 9099 <(+61)%202%208099%209000>
>
> On Tue, Aug 16, 2016 at 12:36 PM, John R Pierce 
> wrote:
>
>> On 8/15/2016 7:23 PM, James Sewell wrote:
>>
>>> Those are all good questions.
>>>
>>> Essentially this is a situation where DR is network separated from Prod
>>> - so I would expect the archive command to fail. I'll have to check the
>>> script it must not be passing the error back through to PostgreSQL.
>>>
>>> This still shouldn't cause database corruption though right? - it's just
>>> not getting WALs.
>>>
>>
>> if the slave database is asking for the WAL's, it needs them. if it needs
>> them and can't get them, then it can't catch up and start.
>>
>>
>>
>> --
>> john r pierce, recycling bits in santa cruz
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
> --
> The contents of this email are confidential and may be subject to legal or
> professional privilege and copyright. No representation is made that this
> email is free of viruses or other defects. If you have received this
> communication in error, you may not copy or distribute any part of it or
> otherwise disclose its contents to anyone. Please advise the sender of your
> incorrect receipt of this correspondence.

-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350

Skype: sameer.ashnik | www.ashnik.com

Re: [GENERAL] upgrade to repmgr3

[Pekka Rinne]

hi

2016-08-04 16:19 GMT+03:00 Ian Barwick :

> Hi
>
> On 08/04/2016 05:57 PM, Pekka Rinne wrote:
>
>> hi!
>>
>> I have been using postgres 9.4 and repmgr2.0 combination and been doing
>> replication (hot standby). Now I'd like to start doing slot based
>> replication and have installed repmgr3 and exeuted the provided sql
>> scripts
>> and also added use_replication_slots=1 into repmgr.conf.
>>
>> The question is that what is the correct procedure to switch into using
>> slots (max_replication_slots) in this case as the system has been set up
>> already without them? Do I have to unregister and re-register all the
>> standbys? Hopefully re-clone could be avoided.
>>
>
> No reclone needed.
>
> What I tried was that I configured max_replication_hosts=5, restarted
>> master, created some slots using select * from
>> pg_create_physical_replication_slot(), configured one created slot
>> into recovery.conf in the slave. What I noticed was that replication
>> seemed
>> to be still working after this but in repl_nodes table slot_name remained
>> empty. Then I did standby re-register with force and slot_name was filled
>> with repmgr_slot_2 value which is not the name I gave for the slot. I
>> think
>> repmgr invented this name but in the pg_replication_slots table
>> repmgr_slot_2 does not exist. There is only the slot I created myself
>> (active=t). So I guess this approach is not quite right.
>>
>> What if I just skip doing the re-register. Does is matter if slot_name
>> remains empty in repl_nodes?
>>
>
> This won't affect replication, however if you attempt any failover actions
> using repmgr (e.g. following a new master), it will probably cause
> problems when
> attempting to create a replication slot on the new master.
>
> As a workaround you can manually update the slot name in the repl_nodes
> table
> to match the one you've chosen. We'll update repmgr to better handle this
> kind of situation. I don't think we've had this particular use-case before,
> so I'll add some notes to the documentation on how best to handle it.
>
>
I tried this workaround. But what I noticed was that after promoting a
standby as a master my replication slots were renamed in repl_nodes to
repmgr_slot_2 and repmgr_slot_3. I did not use those names while creating
them.

Is the correct procedure to assume that nodes slot name should be
repmgr_slot_? This seems to be the case and slots can be created
following this rule.

Another thing is that is it mandatory to pre-create the slots by using
pg_create_physical_replication_slot()? Even if I do not do that replication
seems to sill be working but pg_replication_slots table remains empty. It
is only after promoting a standby as a master that one slot seems to get
created automatically.

br,
Pekka

Re: [GENERAL] C++ port of Postgres

[dandl]

Just wondering what the end goal is for this project... Is it to just maintain 
an up to date Postgres fork that will compile with a C++ compiler? Is it to get 
a conversation going for a direction for Postgres itself to move?  The former I 
don't see gaining much traction or doing all that much for the state of the 
project. The latter possibly could if the community gets on board.

 

I would certainly hope the latter. Having done some work on extension functions 
and an extension language for Postgres, the current situation can be quite 
limiting. 

* Parts of my code could only be written in C++, so I finished up with 
a mixed build, which is not ideal.

* My other issue was dealing with the Datum macros. Type-safe inline 
C++ functions would help reduce dumb errors.

 

Not compelling reasons perhaps, but just a vote for a move in that direction, 
some time.

 

Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org