How are DDL changes replicated in PostgreSQL

Question

In PostgreSQl 9.1, the streaming replication is done by streaming WALs records which is generated by UPDATEs and DELETEs on the Master’s data.

How are the DDL changes replicated? Those are not the part of WALs.

Answer 1

Postgresql's Write Ahead Log (WAL) does contain DDL. In Postgresql, DDL is transactional, just like DML. All goes through the WAL.

See http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

Answer 2

To elaborate on Colin's answer, almost everything goes through the write-ahead log. It is a block level journal that records every write that will be made to any database structure. Every change to any part of the data directory is first recorded in the WAL. That's because the primary purpose of the WAL is to allow replay of changes if the system crashes or loses power, so it needs to record every single planned disk write.

In PostgreSQL, tables, views, etc are just entries in the system catalog tables. Changes to these catalogs get write-ahead logged along with everything else. The same is true of database creation; a db is just an entry in pg_database and the corresponding directory structure.

Changes to tables made by VACUUM, CLUSTER, TRUNCATE etc; they all go through WAL, either with block-level change records or special WAL entries to describe the operation.

Only a few non-durable things don't go through WAL, like:

• changes to UNLOGGED and TEMPORARY tables
• Temp files for on-disk sorts