Hi all,
I am wondering how you guys back up your databases. Say, I have a 20 GB
database, data and indexes. If I run pg_dump on this, it backs up the schema
and the data. When I have to restore this, I whould have to run this through
psql which would then re-build the indexes after it has inserted the records.
That's not really a feasable option as it takes too long.
If I back up the pgdata directory, I will get inconsistent data, as somebody
might update data in 2 tables while my backup is running, one change in a
table I have already backed up, one in a table I have not. The solution would
be to shut the database server down, do the backup and then start everything
back up. But that's not really an option in a 24/7 environment.
What I'd like to see is a transaction aware backup which backs up indexes as
well as data.
Any ideas?
Best regards,
Chris