createdb: database creation failed: ERROR: source database “template1″ is being accessed by other users
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 -bash-3.2$
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 -bash-3.2$
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 -bash-3.2$
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 -bash-3.2$
You have now created a new database that is synced to your hot standby PostgreSQL database server.