How DDL changes are replicated in PostgreSQL

Question

In PostgreSQl 9.1, streaming replication is accomplished by streaming recording WALs that are generated by UPDATE and DELETE on Masters data.

How are DDL changes replicated? This is not part of the WAL.

Answer 1

Postgresql Write Ahead Log (WAL) contains DDL. In Postgresql, DDL is transactional, as is DML. Everything goes through the WAL.

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

Answer 2

To clarify Colin’s answer, almost everything goes through a recording journal. This is a block level log that records every record that will be made in any database structure. Each change in any part of the data catalog is first written to the WAL. This is because the main purpose of the WAL is to allow repeat changes if the system crashes or loses power, so it needs to burn every single scheduled disk.

PostgreSQL tables, views, etc. - these are just entries in the system catalog tables. Changes to these directories are written to the record along with everything else. The same applies to creating a database; db is just an entry in pg_database and the corresponding directory structure.

Change tables made by VACUUM , CLUSTER , TRUNCATE , etc .; they all go through the WAL, either with a record of the change in the block level, or using special WAL records to describe the operation.

Only a few short-lived things do not go through the WAL, for example:

• changes to UNLOGGED and TEMPORARY tables
• Temp files for sorting to disk