Change Details

master (somerset): postgres=# SELECT pg_start_backup('replication', true, false); pg_start_backup ----------------- 1620/5D000028 (1 row) postgres=# select * from pg_stop_backup(false, false); lsn | labelfile | spcmapfile ---------------+-------------------------------------------------------------------+------------ 1625/772A8B40 | START WAL LOCATION: 1620/5D000028 (file 00000001000016200000005D)+| | CHECKPOINT LOCATION: 1620/5D000060 +| | BACKUP METHOD: streamed +| | BACKUP FROM: master +| | START TIME: 2018-06-19 09:53:36 UTC +| | LABEL: replication +| | | (1 row) Data directory whereabouts (master, connected to db to replicate): ``` postgres=# show data_directory; data_directory ------------------------------------------- /srv/softwareheritage/postgres/10/indexer (1 row) ``` Edit backup_label in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ cat backup_label START WAL LOCATION: 1620/5D000028 (file 00000001000016200000005D) CHECKPOINT LOCATION: 1620/5D000060 BACKUP METHOD: streamed BACKUP FROM: master START TIME: 2018-06-19 09:53:36 UTC LABEL: replication ``` Edit tablespace_map in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ touch tablespace_map postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ cat tablespace_map postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ ``` Edit recovery.conf in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ cat recovery.conf standby_mode = 'on' primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass'' host=somerset.internal.softwareheritage.org port=5434 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any' restore_command = 'cp -f /srv/softwareheritage/postgres/wal-archive/%f %p' #archive_cleanup_command = 'pg_archivecleanup /srv/softwareheritage/postgres/wal-archive %r' ```

# master (somerset) ## bootstrap db data replication from master to slave: ``` rsync -e 'ssh -i ~/.ssh/id_rsa.basebackup' -av --progress --exclude pg_wal --exclude postmaster.pid --exclude pg_replslot --delete --delete-excluded /srv/softwareheritage/postgres/10/indexer/ dbreplica1.euwest.azure.internal.softwareheritage.org:indexer/ ``` Note: This can take a while (depending on the db size) ## connect to db to replicate ``` postgres@somerset $ psql -p 5444 postgres=# SELECT pg_start_backup('replication', true, false); pg_start_backup ----------------- 1620/5D000028 (1 row) postgres=# select * from pg_stop_backup(false, false); lsn | labelfile | spcmapfile ---------------+-------------------------------------------------------------------+------------ 1625/772A8B40 | START WAL LOCATION: 1620/5D000028 (file 00000001000016200000005D)+| | CHECKPOINT LOCATION: 1620/5D000060 +| | BACKUP METHOD: streamed +| | BACKUP FROM: master +| | START TIME: 2018-06-19 09:53:36 UTC +| | LABEL: replication +| | | (1 row) ``` Data directory whereabouts (master, connected to db to replicate): ``` postgres=# show data_directory; data_directory ------------------------------------------- /srv/softwareheritage/postgres/10/indexer (1 row) ``` # slave (dbreplica1) Edit backup_label in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ cat backup_label START WAL LOCATION: 1620/5D000028 (file 00000001000016200000005D) CHECKPOINT LOCATION: 1620/5D000060 BACKUP METHOD: streamed BACKUP FROM: master START TIME: 2018-06-19 09:53:36 UTC LABEL: replication ``` Add missing empty directory in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ mkdir pg_wal pg_replslot ``` Edit tablespace_map in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ touch tablespace_map postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ cat tablespace_map postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ ``` Edit recovery.conf in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ cat recovery.conf standby_mode = 'on' primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass'' host=somerset.internal.softwareheritage.org port=5434 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any' restore_command = 'cp -f /srv/softwareheritage/postgres/wal-archive/%f %p' #archive_cleanup_command = 'pg_archivecleanup /srv/softwareheritage/postgres/wal-archive %r' ```

master (somerset):# master (somerset) ## bootstrap db data replication from master to slave: ``` rsync -e 'ssh -i ~/.ssh/id_rsa.basebackup' -av --progress --exclude pg_wal --exclude postmaster.pid --exclude pg_replslot --delete --delete-excluded /srv/softwareheritage/postgres/10/indexer/ dbreplica1.euwest.azure.internal.softwareheritage.org:indexer/ ``` Note: This can take a while (depending on the db size) ## connect to db to replicate ``` postgres@somerset $ psql -p 5444 postgres=# SELECT pg_start_backup('replication', true, false); pg_start_backup ----------------- 1620/5D000028 (1 row) postgres=# select * from pg_stop_backup(false, false); lsn | labelfile | spcmapfile ---------------+-------------------------------------------------------------------+------------ 1625/772A8B40 | START WAL LOCATION: 1620/5D000028 (file 00000001000016200000005D)+| | CHECKPOINT LOCATION: 1620/5D000060 +| | BACKUP METHOD: streamed +| | BACKUP FROM: master +| | START TIME: 2018-06-19 09:53:36 UTC +| | LABEL: replication +| | | (1 row) ``` Data directory whereabouts (master, connected to db to replicate): ``` postgres=# show data_directory; data_directory ------------------------------------------- /srv/softwareheritage/postgres/10/indexer (1 row) ``` # slave (dbreplica1) Edit backup_label in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ cat backup_label START WAL LOCATION: 1620/5D000028 (file 00000001000016200000005D) CHECKPOINT LOCATION: 1620/5D000060 BACKUP METHOD: streamed BACKUP FROM: master START TIME: 2018-06-19 09:53:36 UTC LABEL: replication ``` Add missing empty directory in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ mkdir pg_wal pg_replslot ``` Edit tablespace_map in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ touch tablespace_map postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ cat tablespace_map postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ ``` Edit recovery.conf in data directory: ``` postgres@dbreplica1:/srv/softwareheritage/postgres/10/indexer$ cat recovery.conf standby_mode = 'on' primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass'' host=somerset.internal.softwareheritage.org port=5434 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any' restore_command = 'cp -f /srv/softwareheritage/postgres/wal-archive/%f %p' #archive_cleanup_command = 'pg_archivecleanup /srv/softwareheritage/postgres/wal-archive %r' ```