4 ms·

The killer for me is PITR (point in time recover). A few years ago a colleague triggered and UPDATE without WHERE and PITR saved our sorry asses (on MS SQLServe

The killer for me is PITR (point in time recover). A few years ago a colleague triggered and UPDATE without WHERE and PITR saved our sorry asses (on MS SQLServer, a fair product despite my antipathy MS). Postgres has it, but it is kind of a pain to setup correctly and I love RDS because I don't have to deal with the setup anymore.

snuxoll 10y ago
> Postgres has it, but it is kind of a pain to setup correctly and I love RDS because I don't have to deal with the setup anymore. Check out barman from 2ndquadrant, we've been running it in production for 2 years now - setup was crazy easy and the one time I had to do a restore (for the same reason, UPDATE without a WHERE) it was no-fuss to get it done. Of course, don't let me stop you from using RDS - but there's certainly user friendly backup solutions for Postgres when you're not :)
ledgerdev 10y ago
Sorry it's not clear, but does google cloud sql for postgres have PITR or plan to support it in near future?
- scardine 10y ago
  From their homepage, the features not available for Beta (because Cloud SQL for PostgreSQL is in Beta, some PostgreSQL features are not yet available): 1) Replication 2) High-availability configuration 3) Point-in-time recovery (PITR) 4) Import/export in CSV format
hueving 10y ago
What's the different between PITR and a volume snapshot restoration?
- scardine 10y ago
  PITR restores your last full backup (or snapshot) and plays the transaction log until the point in time you set so it can be very precise. Volume snapshot restoration is atomic and probably faster, but it gets you only the specific point in time where the snapshot was taken. I guess the worst case in terms of storage usage in both mechanisms are not that different, but PITR should be fully transparent performance-wise.
d_burfoot 10y ago
One of the most annoying things for me about Postgres when I switched from MySQL is that in the former you can't put LIMIT clauses on UPDATE/DELETE statements. When I'm doing surgical work on a DB, I always, always want to do something like : - SELECT statement showing me the relevant rows (and rowcount=N) - UPDATE/DELETE statement with a corresponding limit equal to N+1 It gives nice, easy peace of mind that you aren't accidentally wiping out your whole table because you messed up the logic on your WHERE clause. Of course if you made a mistake, you probably still killed N records, but often that is much much better than killing the whole table (and maybe also downstream tables that are linked with FKEY cascade delete!)
- mastazi 10y ago
  It seems to me that the amount of work required to fix the above, in most cases, would be almost equal to the amount of work that would be required if you hadn't used the LIMIT clause.
- saurik 10y ago
  If you are that concerned, put "begin;" before your command, check that the updated/inserted number is reasonable, and then either do a commit or a rollback. That way you also didn't screw up N+1 totally random rows. You can even put a returning clause on the update to see which rows you were affecting.
  - piaste 10y ago
    Or if you can't afford to lock the table in the process, before you run UPDATE {foo} WHERE {bar} you back up the relevant rows into a temporary table with SELECT * INTO foo_backup WHERE {bar}.
    - saurik 10y ago
      None of this should cause a table lock: at best you will just lock the affected rows and at worst you will end up with a predicate lock on the where clause.
    - 10y ago

snuxoll 10y ago

> Postgres has it, but it is kind of a pain to setup correctly and I love RDS because I don't have to deal with the setup anymore. Check out barman from 2ndquadrant, we've been running it in production for 2 years now - setup was crazy easy and the one time I had to do a restore (for the same reason, UPDATE without a WHERE) it was no-fuss to get it done. Of course, don't let me stop you from using RDS - but there's certainly user friendly backup solutions for Postgres when you're not :)

ledgerdev 10y ago

Sorry it's not clear, but does google cloud sql for postgres have PITR or plan to support it in near future?

scardine 10y ago
From their homepage, the features not available for Beta (because Cloud SQL for PostgreSQL is in Beta, some PostgreSQL features are not yet available): 1) Replication 2) High-availability configuration 3) Point-in-time recovery (PITR) 4) Import/export in CSV format

hueving 10y ago

What's the different between PITR and a volume snapshot restoration?

scardine 10y ago
PITR restores your last full backup (or snapshot) and plays the transaction log until the point in time you set so it can be very precise. Volume snapshot restoration is atomic and probably faster, but it gets you only the specific point in time where the snapshot was taken. I guess the worst case in terms of storage usage in both mechanisms are not that different, but PITR should be fully transparent performance-wise.

d_burfoot 10y ago

One of the most annoying things for me about Postgres when I switched from MySQL is that in the former you can't put LIMIT clauses on UPDATE/DELETE statements. When I'm doing surgical work on a DB, I always, always want to do something like : - SELECT statement showing me the relevant rows (and rowcount=N) - UPDATE/DELETE statement with a corresponding limit equal to N+1 It gives nice, easy peace of mind that you aren't accidentally wiping out your whole table because you messed up the logic on your WHERE clause. Of course if you made a mistake, you probably still killed N records, but often that is much much better than killing the whole table (and maybe also downstream tables that are linked with FKEY cascade delete!)

mastazi 10y ago
It seems to me that the amount of work required to fix the above, in most cases, would be almost equal to the amount of work that would be required if you hadn't used the LIMIT clause.
saurik 10y ago
If you are that concerned, put "begin;" before your command, check that the updated/inserted number is reasonable, and then either do a commit or a rollback. That way you also didn't screw up N+1 totally random rows. You can even put a returning clause on the update to see which rows you were affecting.
- piaste 10y ago
  Or if you can't afford to lock the table in the process, before you run UPDATE {foo} WHERE {bar} you back up the relevant rows into a temporary table with SELECT * INTO foo_backup WHERE {bar}.
  - saurik 10y ago
    None of this should cause a table lock: at best you will just lock the affected rows and at worst you will end up with a predicate lock on the where clause.
  - 10y ago