From 3fa17ac835da9f7ed6d128f1ac7e6de2e2271140 Mon Sep 17 00:00:00 2001 From: Drew DeVault Date: Tue, 30 Jun 2020 12:01:38 -0400 Subject: Update PSQL plans --- ops/robust-psql.md | 23 ++++++++++++++--------- 1 file changed, 14 insertions(+), 9 deletions(-) (limited to 'ops') diff --git a/ops/robust-psql.md b/ops/robust-psql.md index bd24387..a3c8d7c 100644 --- a/ops/robust-psql.md +++ b/ops/robust-psql.md @@ -37,23 +37,28 @@ reconfigure it as a [hot standby][hot standby] (which will accept queries). replication. It must have the REPLICATION and LOGIN privileges and [pg_hba.conf][hba changes] must be updated. 1. Take a [base backup][base backup] of the primary and ship it to the - secondary. Be advised: we have full_page_writes disabled as a ZFS + secondary. Be advised: we have `full_page_writes` disabled as a ZFS optimization; this may cause performance degredation during the basebackup - process. + process. `pg_basebackup -PzFt -D basebackup`. Place 'basedirectory' in + `/var/lib/postgresql`. +1. Nuke the postgres data directory's contents on the secondary, su to postgres, + then run these commands: `tar -C ~/12/data -xf ~/basebackup/base.tar.gz`, + `tar -C ~/12/data/pg_wal -xf ~/basebackup/pg_wal.tar.gz`, + `touch ~/12/data/standby.signal` 1. Fill out `primary_conninfo` on the secondary with the connection details for - the account created for replication. + the account created for replication. `host=... port=5432 user=... password=...` 1. Configure a replication slot on the primary for the secondary: SELECT * FROM pg_create_physical_replication_slot('konpaku'); Then set `primary_slot_name` to `konpaku` on the secondary. -1. Set `hot_standby` to true on the secondary. -1. [Restore the base backup][restore base backup] on the secondary. It will - automatically sync with the primary when complete; monitor the logs. TODO: - What should our `restore_command` be? Want to figure it out in advance. +1. Set `hot_standby` to "on" on the secondary. +1. Start the database, monitor logs for restore completion. 1. Sanity check the secondary database. -1. Roll back the `restore_command` change so that we aren't surprised by it - later. Drop the base backup files as well. + +Note: some of this procedure is adapted from the +[restore the base backup][restore base backup] docs, but modified cause those +docs are shit. [warm standby]: https://www.postgresql.org/docs/current/warm-standby.html [hot standby]: https://www.postgresql.org/docs/current/hot-standby.html -- cgit