0

I'm trying postgres database backup and restore. The access to postgres is through pgpool.

To achieve the back up I am following the official postgres pg_dumpall documentation.

Commands taken from postgres website: https://www.postgresql.org/docs/14/app-pg-dumpall.html

$ pg_dumpall > db.out
To reload database(s) from this file, you can use:
$ psql -f db.out postgres

The backup works fine.

However, when attempting to restore, I'm getting the following error because of replication feature enabled by pgpool.

psql:tmp/backup/postgresDump/pg_data.out:15: ERROR:  database "xyz" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Here are the following ideas I tried by browsing other SO questions.

  1. I tried to update active='f' in pg_catalog.pg_replication_slots view. It failed with the error below
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
  1. List the process ids for the replication slot and used pg_terminate_backend along with the pid, followed by restore. Command to terminate the replication slot
psql -U postgres -h pgpool.default.cluster.local -c "SELECT pg_terminate_backend(3402)"
 pg_terminate_backend 
----------------------
 f
(1 row)

As per the second answer in this link, Postgresql - unable to drop database because of some auto connections to DB I executed the terminate_backend command multiple times until it returned 0 results. Although this step was successful, restore failed with error saying

psql:tmp/postgresDump/pg_data.out:14: ERROR:  database "xyz" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Looks like as soon as I drop a replication slot, the pgpool recreates the replication slot and establishes a connection for it.

  1. Tried by dropping the replication_slot following the official documentation. https://www.postgresql.org/docs/9.5/functions-admin.html
psql -U postgres -h pgpool.default.local -c "select pg_drop_replication_slot('repmgr_slot_1001');"
ERROR:  replication slot "repmgr_slot_1001" is active for PID 3402

Any information on how to execute restore functionality through psql is highly appreciated.

Ninja Flinstone
  • 21
  • 1
  • Why are you trying to restore into a system that is already populated? – jjanes Mar 05 '22 at 18:19
  • 'db.out' and 'pg_data.out' are different files. – jjanes Mar 05 '22 at 18:21
  • I would like to perform backup periodically and restore them if need be to troubleshoot issues. pg_data.out and db.out are two different file names. In my example, I used pg_data.out as a filename. – Ninja Flinstone Mar 06 '22 at 14:41

0 Answers0