Different timeline and 'nextOID' after backup/restore

Поиск
Список
Период
Сортировка
От Jose Baez
Тема Different timeline and 'nextOID' after backup/restore
Дата
Msg-id CAJ5xMxk9a9cWNEjS_arPhnAgXiURN=pn5xUTMqAKo5QV2zKgVQ@mail.gmail.com
обсуждение исходный текст
Список 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.






В списке pgsql-admin по дате отправления:

Предыдущее
От: Magnus Hagander
Дата:
Сообщение: Re: postgres version equivalent EnterpriseDB version8.3.0.112 , can I get it?
Следующее
От: Khangelani Gama
Дата:
Сообщение: Re: postgres version equivalent EnterpriseDB version8.3.0.112 , can I get it?