Postgresql 9.0 adding new database to streaming replication

Question

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?

Answer 1

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.

Answer 2

Hz.Root, your question has many open ends but I will try to answer.

What is the best way of doing this?

Just create a new database in master server and populate it.

I have a backup file about 20GB. should i restore to that cluster?

Yes. To be precise, you restore it to the database, not to the cluster.

can standby server have all the data?

Standby server MUST have all the data - that is it's purpose. Every database, and every other object that is in primary PostgreSQL cluster, will be replicated to secondary server.