5 ms·

Ideally off-site replica you fail over too and don't need to restore. pg_restore will handle roles, indexes, etc assuming you didn't switch the flags around to

by nijave 1y ago

Ideally off-site replica you fail over too and don't need to restore. pg_restore will handle roles, indexes, etc assuming you didn't switch the flags around to disable them If you're on EC2, hopefully you're using disk snapshots and WAL archiving.

pgwhalen 1y ago
Of course that’s preferable, but OP is specifically asking about the cold restore case, which tends to pose different problems, and is just as important to maintain and test.
Arbortheus 1y ago
Offsite replica is only applicable if the cause is a failure of the primary. What if I’m restoring a backup because someone accidentally dropped the wrong table?
- ants_everywhere 1y ago
  I would hope dropping a table on a production database is something that is code reviewed
  - benreesman 1y ago
    nah, on a long enough timeline everything will go wrong. blaming the person who managed to drop the table finally is dumb: if you can't fix literally everything that could happen to it, it's not done.
  - anonymars 1y ago
    Isn't the entirety of disaster recovery about situations that aren't supposed to happen? High availability is different from disaster recovery
- nijave 1y ago
  You can PITR on a replica which would be much faster than restoring a full backup of a large DB
asah 1y ago
DROP DATABASE :-)
whatevaa 1y ago
Postgres is not great with off-site replicas, unless not many writes are done. Replication protocol is very chatty. One of the reasons Uber mentioned when moving to mysql in their engineering blog.
- hnarn 1y ago
  > One of the reasons Uber mentioned when moving to mysql in their engineering blog If I'm not mistaken, this was in 2016 (that's 10 years next year, time flies when you're having fun) -- which is practically an eternity in IT. I'm no DBA but I'm fairly sure many changes have been made to Postgres since then, including logical replication (which can be selective), parallel apply of large transactions in v16, and so on. I'm not saying this means their points are invalid, I don't know Postgres well enough for that, but any point made almost 10 years ago against one of the most popular and most actively developed options in its field should probably be taken with a pinch of salt.
  - ffsm8 1y ago
    > I'm not saying this means their points are invalid, I don't know Postgres well enough for that, but any point made almost 10 years ago against one of the most popular and most actively developed options in its field should probably be taken with a pinch of salt. Heh, I remember the countless articles after that debacle back then pointing out all the reasons why their migration was entirely pointless and could've been summed up to "devs not knowing the tools they're working with" before starting multi million projects to fuel their cv driven development. So even if you aren't willing to do so, their rational for the migration was fully debunked even back then
- fulafel 1y ago
  This is oft quoted but if you read the posts, Uber discovered they didn't want SQL (or apparently transactions etc), and implemented a nosql that happened to use mysql as a backend, and that was a much bigger change than moving off PG.

by nijave 1y ago

pgwhalen 1y ago

Of course that’s preferable, but OP is specifically asking about the cold restore case, which tends to pose different problems, and is just as important to maintain and test.

Arbortheus 1y ago

Offsite replica is only applicable if the cause is a failure of the primary. What if I’m restoring a backup because someone accidentally dropped the wrong table?

ants_everywhere 1y ago
I would hope dropping a table on a production database is something that is code reviewed
- benreesman 1y ago
  nah, on a long enough timeline everything will go wrong. blaming the person who managed to drop the table finally is dumb: if you can't fix literally everything that could happen to it, it's not done.
- anonymars 1y ago
  Isn't the entirety of disaster recovery about situations that aren't supposed to happen? High availability is different from disaster recovery
nijave 1y ago
You can PITR on a replica which would be much faster than restoring a full backup of a large DB

asah 1y ago

DROP DATABASE :-)

whatevaa 1y ago

Postgres is not great with off-site replicas, unless not many writes are done. Replication protocol is very chatty. One of the reasons Uber mentioned when moving to mysql in their engineering blog.

hnarn 1y ago
> One of the reasons Uber mentioned when moving to mysql in their engineering blog If I'm not mistaken, this was in 2016 (that's 10 years next year, time flies when you're having fun) -- which is practically an eternity in IT. I'm no DBA but I'm fairly sure many changes have been made to Postgres since then, including logical replication (which can be selective), parallel apply of large transactions in v16, and so on. I'm not saying this means their points are invalid, I don't know Postgres well enough for that, but any point made almost 10 years ago against one of the most popular and most actively developed options in its field should probably be taken with a pinch of salt.
- ffsm8 1y ago
  > I'm not saying this means their points are invalid, I don't know Postgres well enough for that, but any point made almost 10 years ago against one of the most popular and most actively developed options in its field should probably be taken with a pinch of salt. Heh, I remember the countless articles after that debacle back then pointing out all the reasons why their migration was entirely pointless and could've been summed up to "devs not knowing the tools they're working with" before starting multi million projects to fuel their cv driven development. So even if you aren't willing to do so, their rational for the migration was fully debunked even back then
fulafel 1y ago
This is oft quoted but if you read the posts, Uber discovered they didn't want SQL (or apparently transactions etc), and implemented a nosql that happened to use mysql as a backend, and that was a much bigger change than moving off PG.