Different timeline and 'nextOID' after backup/restore

From: Jose Baez <pepote(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Different timeline and 'nextOID' after backup/restore
Date: 2014-11-03 12:48:05
Message-ID: CAJ5xMxk9a9cWNEjS_arPhnAgXiURN=pn5xUTMqAKo5QV2zKgVQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello!

I have two PSQL nodes and I have problems trying to get a data replica from
1 node to another.
There are no PSQL connections to any node.

I do PITR from node 1 to 2:

- Stop PSQL from node 2.
- Reload node 1 with postgresql.conf:
- wal_level = archive
- archive_mode = on
- archive_command = 'test ! -f /var/lib/postgresql/pg_xlog_archive/%f
&& /bin/cp %p /var/lib/postgresql/pg_xlog_archive/%f''

- Run pg_start_backup('label',true)
- Create 'recovery.conf' with 'restore_command = 'scp
psql-node1:/var/lib/postgresql/pg_xlog_archive/%f %p'
- tar.gz all PSQL 1 folders
- Run pg_stop_backup()
- Reload node 1 with archive_comand = 'exit 0'
- Move tar.gz from node 1 to node 2
- Delete 'recovery.conf' from node 1
- Run pg_switch_xlog() on node 1

- Untar tar.gz in node 2
- Start PSQL in node 2
- Node 2 starts recovery phase...
- Restore process finishes.

I get exactly the same data in both nodes (checking with usual PSQL
connections).

*But when I create a new database or tables, they get different OID in both
nodes.*

I checked PSQL logs during backup/restore and I don't see antyhing weird,
except that node 2 starts with a different TimelineID (+1 in relation to
node 1).

However, if run the backup/restore process stopping PSQL 1 at first, then
start it, and then run backup/restore process, then I get same data in both
nodes and "nextOID" are also the same.

It seems like there is data somewhere that is not copied to the files
unless I restart the PSQL service.

*Is it the expected behaviour?*

I tried with different environments:

- PSQL 9.1 (different versions) and with 9.3.5
- Debian packages and Suse.

Browse pgsql-admin by date

  From Date Subject
Next Message Khangelani Gama 2014-11-03 15:24:38 Re: postgres version equivalent EnterpriseDB version8.3.0.112 , can I get it?
Previous Message Magnus Hagander 2014-11-03 12:13:45 Re: postgres version equivalent EnterpriseDB version8.3.0.112 , can I get it?