How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?

Question

I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for atomicity), but I'm completely unaware of how things like, A) initial table setup is transferred from server 1 to server 2 (e.g., I run syncdb from my Django app, which creates a series of tables on server 1), and B) how continued schema changes are carried from server 1 to server 2 (e.g., I run a Django South migration, which sends ALTER TABLE queries, as well as adding/removing indexes, etc.). Are these things handled transparently with streaming replication, or is there something I have to do to affect this change across both servers?

Answer 1

If you intend to use PostgreSQL's built-in streaming replication, then you seed the replica with a base backup - a file-system level copy of the original database. So it already has all the tables and data from the master. pg_basebackup is usually used for this.

Streaming replication reads changes at the block level from the write-ahead logs, so the databases are kept physically the same. Since DDL is, just like DML, recorded in the WAL, DDL and DML are both transparently replicated.

See the documentation on HA and replication in PostgreSQL for more information.