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

Tatsuo Ishii ishii at postgresql.org
Thu Sep 4 16:00:07 JST 2014


Since you need to stop posmaster anyway, there's no way to avoid failover.
You don't need to stop pgpool server however.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list