When attempting to create a new database on a PostgreSQL server running walmgr, which is part of SkyTools by Skype Developers, for real time failover I received an error. Initially I was a bit baffled by the error however I remembered after racking my brain for a little bit that you must stop walmgr’s syncdaemon when adding a new database. Below are more details about the error as well as how to stop walmgr’s syncdaemon and then create the new database.

Error Creating New PostgreSQL Database:

[root@dev ~]# createdb -U postgres -E UTF8 testdata
createdb: database creation failed: ERROR:  source database "template1" is being accessed by other users
DETAIL:  There are 1 other session(s) using the database.
[root@dev ~]#

As you can see above you receive an error when attempting to use createdb to add a database to the PostgreSQL server. To resolve the issue stop the walmgr syncdaemon using the syntax below, add the database, setup walmgr again, backup the database to the secondary database server using walmgr, and then start the syncdaemon again.

Stop walmgr syncdaemon On A Linux Server:

-bash-3.2$ walmgr.py conf/wal-master.ini stop
2010-11-17 10:20:55,069 5497 INFO Disabling WAL archiving
2010-11-17 10:20:55,072 5497 INFO Sending SIGHUP to postmaster
2010-11-17 10:20:55,072 5497 INFO Pidfile /var/lib/pgsql/walmgr.pid exists, attempting to stop syncdaemon.
2010-11-17 10:20:55,167 5497 INFO Done

Once the syncdaemon is stopped create the new database using the createdb command from the CLI as shown in the below example.

Create PostgreSQL Database Using createdb:

[root@dev ~]# createdb -U postgres -E UTF8 testdata
[root@dev ~]#

Once created use the below commands to first run the walmgr setup script, then run the walmgr backup script, and last but not least start the syncdaemon again. All of the below commands should be issued as the postgres user.

Issue walmgr setup Python Script:

-bash-3.2$ walmgr.py conf/wal-master.ini setup
2010-11-17 09:47:39,950 4068 INFO Configuring WAL archiving
2010-11-17 09:47:39,954 4068 INFO Sending SIGHUP to postmaster
2010-11-17 09:47:40,128 4068 INFO Done

Issue walmgr backup Python Script:

-bash-3.2$ walmgr.py conf/wal-master.ini backup
2010-11-17 09:47:37,659 20355 INFO Backup lock obtained.
2010-11-17 09:47:37,659 20355 INFO got SystemExit(0), exiting
2010-11-17 09:47:56,549 4081 INFO Execute SQL: select pg_start_backup('FullBackup'); [dbname=template1]
2010-11-17 09:47:38,042 20359 INFO Removing expired backup directory: /var/lib/pgsql/walshipping/data.master
2010-11-17 10:20:28,301 4081 INFO Checking tablespaces
2010-11-17 10:20:15,237 21440 INFO First useful WAL file is: 000000010000001000000052
2010-11-17 10:20:16,571 21453 INFO Backup lock released.
2010-11-17 10:20:35,475 4081 INFO Full backup successful

Depending on the size of your database the above command could take awhile to complete. Once the backup is completed start the syncdaemon again.

Start walmgr syncdaemon Python Script:

-bash-3.2$ walmgr.py conf/wal-master.ini syncdaemon -d

You have now created a new database that is synced to your hot standby PostgreSQL database server.

List Price: $36.99 USD
New From: $34.58 USD In Stock
Used from: $11.94 USD In Stock

PostgreSQL 9.0 High Performance (Paperback)

By (author): Gregory Smith

List Price: $49.99 USD
New From: $44.99 USD In Stock
Used from: $35.01 USD In Stock

Tags: , , , , , , , , , , , , , , , , , ,
Leave a Reply

*Type the letter/number combination in the abvoe field before clicking submit.