Hacker News Reader

Comment by castorp | Hacker News Reader

> I know there are some ways that reduce the downtime, like hard linking the data between the clusters instead of version

You need to have a proper backup of the database anyway, so I don't really see the risk in using the --link mode (which makes the downtime pretty much independent of the size of the actual data).

View on HN

Well the problem is that to actually get the backup (using pgdump) I'd also needs time that is proportional to the size of the database; and during that time the database can't get any writes so the system will again be down!

Also I'd really rather avoid restoring the data from a backup unless there's a real disaster that happened.

There are other options, though. For upgrades, a virtual machine snapshot is a perfect backup, and with really large databases, you would likely have something using WAL archiving, which provides a continuous backup until the point that you shut down the database for upgrade. Finally, if you have a database replica, that can also functionally act as your backup.

Thanks! I'll consider the vmware snapshot and the WAL archiving (I wasn't familiar with that till now) :)

pg_basebackup defaults to streaming WAL from whatever server it is taking a copy from, as backups are otherwise inconsistent. There's no need for the source database to be paused while doing it, and even if your backup takes several hours to complete, once it finishes the target will have all the writes that occurred while the backup was being taken.

Thought you might find this useful, as no one should be considering a Postgres backup as a downtime requiring process!

Thank you very much; I was using pg_dump to take backups until now!

I was talking about a backup, not a dump (snapshot).

https://blog.dbi-services.com/what-is-a-database-backup-back...

Reply on news.ycombinator.com