Postgresql 9.0 adding new database to streaming replication

postgresql

postgresql 9.0 streaming replication + hot standby is running. Now i need to add one more database to current cluster. What is the best way of doing this? I have a backup file about 20GB. should i restore to that cluster? can standby server have all the data?

Best Answer

Yes, you can restore the backupfile to your hot-standby. But there are some detail you should notice. The following are steps I suggest

  1. create your database user on primary node
  2. create tablespace directory both on the primary node and standby node. (If you don't create the same directory on standby node, then after you create a database on primary node, standby PostgreSQL server will down. So, take care!)
  3. create tablespace on primary node
  4. create database on primary node. If a same name was automatlly created on the standby node then all is okey.
  5. restore the backup file to your primary database. then the standby database will replicate the data from the primary db.

those steps will have an impact on performace, so take care.

Related Question