[pgpool-general: 3142] A proper way to recreate a database

[Sergey Arlashin]

Hi!

We have the following setup: Pgpool 3.3.2 + 2 PostgreSQL 9.3 backends with streaming replication.

In order to deploy a new version of database on our development servers we need to drop existing db and then recreate it from scratch. But due to existing connections we cannot do this. 
Therefore we tried to execute the following sequence on master db server:

select pg_terminate_backend(pid) from pg_stat_activity where datname = 'ourdb';
alter database ourdb connection limit 0;

and after this we instantly get the following output on our pgpool server:

Sep  4 05:51:15 lb-node1 pgpool[3650]: postmaster on DB node 0 was shutdown by administrative command
Sep  4 05:51:15 lb-node1 pgpool[3650]: degenerate_backend_set: 0 fail over request from pid 3650
Sep  4 05:51:15 lb-node1 pgpool[31928]: postmaster on DB node 0 was shutdown by administrative command
Sep  4 05:51:15 lb-node1 pgpool[31928]: degenerate_backend_set: 0 fail over request from pid 31928
Sep  4 05:51:15 lb-node1 pgpool[12659]: starting degeneration. shutdown host db-node1.site(5432)
Sep  4 05:51:15 lb-node1 pgpool[12659]: Restart all children
Sep  4 05:51:15 lb-node1 pgpool[21136]: postmaster on DB node 0 was shutdown by administrative command
Sep  4 05:51:15 lb-node1 pgpool[22420]: postmaster on DB node 0 was shutdown by administrative command
Sep  4 05:51:15 lb-node1 pgpool[4859]: postmaster on DB node 0 was shutdown by administrative command
Sep  4 05:51:15 lb-node1 pgpool[12659]: execute command: /etc/pgpool2/scripts/failover.sh 0 0 db-node2.site /var/lib/postgresql/9.3/main/switch_master
Sep  4 05:51:18 lb-node1 pgpool[12659]: find_primary_node_repeatedly: waiting for finding a primary node
Sep  4 05:51:24 lb-node1 pgpool[12659]: find_primary_node: primary node id is 1
Sep  4 05:51:24 lb-node1 pgpool[21136]: degenerate_backend_set: 0 fail over request from pid 21136
Sep  4 05:51:24 lb-node1 pgpool[22420]: degenerate_backend_set: 0 fail over request from pid 22420
Sep  4 05:51:24 lb-node1 pgpool[4859]: degenerate_backend_set: 0 fail over request from pid 4859
Sep  4 05:51:24 lb-node1 pgpool[12659]: failover: set new primary node: 1
Sep  4 05:51:24 lb-node1 pgpool[12659]: failover: set new master node: 1
Sep  4 05:51:24 lb-node1 pgpool[12762]: worker process received restart request
Sep  4 05:51:24 lb-node1 pgpool[12659]: failover done. shutdown host db-node1.site(5432)
Sep  4 05:51:25 lb-node1 pgpool[12761]: pcp child process received restart request
Sep  4 05:51:25 lb-node1 pgpool[12659]: PCP child 12761 exits with status 256 in failover()
Sep  4 05:51:25 lb-node1 pgpool[12659]: fork a new PCP child pid 4789 in failover()
Sep  4 05:51:25 lb-node1 pgpool[12659]: worker child 12762 exits with status 256
Sep  4 05:51:25 lb-node1 pgpool[12659]: fork a new worker child pid 4790
Sep  4 05:51:25 lb-node1 pgpool[12659]: failover: no backends are degenerated


I assume this is a normal behaviour. So the question is, what is the proper way to do what we need without causing failover? Do we need to stop pgpool server? Or may be there is another way?

--
Best regards,
Sergey Arlashin