There's no exact equivalent to what you want. Options include:
Atomic file-system snapshot
If you're using an atomic file system snapshot you don't need to freeze the database. It might make recovery a little faster if you force a CHECKPOINT
first, but that's about it. When you take a filesystem snapshot and copy it, then start the copy, to PostgreSQL it's as if it crashed and is starting back up. That's perfectly fine, it's crash safe and designed to work that way.
This approach is only valid if the snapshot is atomic - it's all at the same virtual instant. You can't get an atomic snapshot across multiple file systems (on Linux, at least), so if your DB is split across multiple tablespaces or WAL is on a separate disk to the heap you cannot use this approach.
pg_start_backup
/ pg_stop_backup
If you can't do an atomic snapshot you can enable WAL archiving, run pg_start_backup
, copy the DB, run pg_stop_backup
and capture the last WAL archives generated.
It's a bit more complicated, but it gives you a consistent backup without stopping writes and without needing file-system level atomic snapshots.
pg_basebackup
An alternative to using pg_start_backup
and pg_stop_backup
is to use pg_basebackup
to do a streaming copy of the DB over the PostgreSQL replication protocol with --xlog-method=stream
. This requires only a PostgreSQL replication connection, doesn't require the DB to be stopped, and is pretty seamless.
--xlog-method=stream
was only added in pretty recent versions, and pg_basebackup
its self is fairly new.
pg_dump
I didn't initially mention it because you were looking for external tools, but there's always pg_dump
, which gets a SERIALIZABLE
snapshot of the database and dumps it. The DB keeps running like normal (it can still accept writes) and the dump is entirely internally consistent from the time you started the dump.
Write quiescence
Stopping all incoming transactions won't stop PostgreSQL writing. It'll still have VACUUM
work to do with autovacuum, checkpoints to perform, stats to write, etc.
There's no feature in Pg to stop all writes at this point. It might be nice to add, but I'm not aware of anyone working on it.
Some file systems, like XFS, support write freezing at the file system level; this causes all writes to block until the freeze is released. It's safe to freeze all file systems then copy all file systems.
SubQuestion 1: I don't know of any way to get rid of the 23 byte header. There is also a 24 byte header on the page. http://www.postgresql.org/docs/8.3/static/storage-page-layout.html
SubQuestion 2: This guide should help. As suggested, increasing maintenance_work_mem is advised, but also increasing checkpoint_segments should help (to reduce the frequency of checkpoints). You've set your work_mem at 64GB, which seems really high. I would suggest keeping it under 2GB even on a system like yours. work_mem is for doing sorts and operations of that nature. http://www.postgresql.org/docs/current/interactive/populate.html
SubQuestion 3: I am not able to find any information on the physical layout of indexes within the pages. I would create a temporary table, fill it with test data (1 million rows?), create a temporary index, get the size of the index from the system, then divide by the number of rows to get the average. I can't show you an SQLFiddle version because it doesn't allow access to system functions. Try these techniques: http://postgresql.cc/postgres-index-size
PS - 25 billion rows? That's more than I've ever dealt with. I'm in the 4 billion range for one particular table ... For comparison, other database servers, like SQL Server 2008+, have a minimum 9 byte header for each row, so you can't entirely get rid of it but I've always thought that PostgreSQL's 23 byte header was a bit excessive.
Best Answer
I'm assuming based on the post that the PostgreSQL database server and the process restoring the dump are on the same machine. If so:
Not really. You could
SIGSTOP
thepg_restore
orpsql
process and/or the correspondingpostgres
backend, but I wouldn't consider that my first-choice option.Since it's a loopback TCP connection or a unix socket connection when you're restoring to a local PostgreSQL instance, it doesn't care if the network changes or goes away. So suspend and resume is just fine.
BTW, 9GB is not large. 1TB is large.