5 ms·

> I really would like to see bi-temporal table support eventually come to PG > Is anybody aware somebody working on that for PG? Not that I am aware of. Post

by clg2 10y ago

> I really would like to see bi-temporal table support eventually come to PG > Is anybody aware somebody working on that for PG? Not that I am aware of. Postgres is a fantastic database, and with zero licensing headaches, but when people ask why companies are still paying for commercial DBMS engines, some features that they have which Postgres does not have, and which I often miss are : 1)"temporal" / versioned tables / time consistent views of data . Oracle has a lot of "flashback" related options at the DB and table levels which can be really useful. 2) Easy, built-in partitioning for large datasets. (This is being worked on and will probably start to be released in Postgres 10). 3) Better backup options for large databases, such as incremental/differential backups. In the Yandex migration from Oracle to Postgres I recall that this, ie incremental backups, is one of the things they mentioned it would be great to have. 4) Along with backups, the built-in recovery options in Postgres are fairly basic compared to what you can do regarding single table recovery, corruption repair etc that other DBs have.

technion 10y ago
The corollary to the backup solution is that if Postgresql ever ended up operating in a similar fashion to Oracle's rman, particularly on its encouraged ASM, Postgresql would very quickly start seeing "Postgresql requires a full time DBA, just use MariaDB" type blogs. I can see how the middle ground is a hard problem.
- clg2 10y ago
  I agree, Postgres having its own storage subsystem would be wrong. Fortunately the PG philosophy is to leave what the OS does best to the OS, so its unlikely that situation would arise. However, a middle ground of being able to backup just the changes in a multi-terabyte database that have been made since the last full backup, wouldn't be controversial and is a common use case. I've read of a few attempts on the PG dev mailing list over the last couple of years, so hopefully one of them will be completed sometime soon and merged into core. Some of the work I have come across : https://github.com/ossc-db/pg_rman https://github.com/ossc-db/pg_rman https://github.com/2ndquadrant-it/barman/issues/21 https://github.com/2ndquadrant-it/barman/issues/21
- randallsquared 10y ago
  > Postgresql would very quickly start seeing "Postgresql requires a full time DBA, just use MariaDB" Yeah, welcome to 15 years ago, when that was the common wisdom.
elmigranto 10y ago
> incremental/differential backups What's wrong with WAL backup? Postgres can run your script and pass it path to newly created WAL chunk, that you can ship off anywhere. Or run `pg_start_backup`, rsync data directory, `pg_stop_backup`.
- clg2 10y ago
  You would back up the WAL as well. However there might be a lot of WAL to apply, as data gets added then deleted or changed. A differential backup (see Note) solves this somewhat for large databases by just backing up the blocks changed since the last full backup. So you might do a full backup once a week, and differentials nightly in between and also save the WAL. This means you are not backing up many TBs every night. To recover, you would restore the full backup, plus the last differential backup plus any WAL since the last differential. This reduces the volume of WAL to be applied in a recovery. Note : Differential backups would include all the changes since the last FULL backup. Incremental backups would include changes since the last backup, whether a full or the last incremental.
- dgregd 10y ago
  > What's wrong with WAL backup? I use PG for 10 years and I never needed incremental backups. But if someone asked me about it then I would respond that the PG has no incremental backups. So WAL backup is not well known or documented in my opinion. An average db admin needs a tool which will generate incremental backup as a single file named like dbname_20160926.sql
  - MustardTiger 10y ago
    >So WAL backup is not well known or documented in my opinion What? It is the standard backup method clearly explained in the docs.
  - SEJeff 10y ago
    Barman[1] gives you something better than incremental backups actually. It sets up the backup server as a database slave via PG's built-in replication. It gives you point in time recovery with the nicest CLI of any backup tool for any DB I've ever used. If you think backup tools are a minus for PG, I guess you've never used barman. Easy as pie and has saved my bacon several times when CASCADE DELETE wrecked a few dbs for me due to coworkers futzing around. [1] http://www.pgbarman.org/ http://www.pgbarman.org/
dgregd 10y ago
> why companies are still paying for commercial DBMS engines In my experience in 90% of cases application code migration to a new db engine is the real road block. I've seem many cases where even migration to a new Oracle version is a huge problem because of required app code review and full user acceptance tests. So companies pay licence fees for old unsupported versions. What is even more absurd. And frankly speaking in many cases of simple apps even SQLite has all necessary features in replace Oracle.
tajen 10y ago
> 1)"temporal" / versioned tables / time consistent views of data Oracle doesn't have transactional DDL. It means if you drop a column or create a table, it commits your transaction automatically. This is huge, because with PostgreSQL you can import a full database in a single transaction, and roll it back if anything goes wrong. You can upgrade a full application and roll back if anything goes wrong. No more partial-state headaches. Therefore I'm not sure what "versioned tables" mean. Have you tried PostgreSQL's temporal tables extension, which seems to do what you mean?
X86BSD 10y ago
3) ZFS with snapshots and send/receive are what I use for local and offsite backups. They can be as fine grained as you like time wise, and are so so so much easier to manage and restore vs traditional backups.
- takeda 10y ago
  This is probably easier, but it might be an issue, when you might want to skip some transactions (someone for example deletes a table). Make sure you also back up WALs.
marcosdumay 10y ago
You can very likely get any of those implemented for you by the price of a largish site license pf Oracle.

by clg2 10y ago

technion 10y ago

The corollary to the backup solution is that if Postgresql ever ended up operating in a similar fashion to Oracle's rman, particularly on its encouraged ASM, Postgresql would very quickly start seeing "Postgresql requires a full time DBA, just use MariaDB" type blogs. I can see how the middle ground is a hard problem.

clg2 10y ago
I agree, Postgres having its own storage subsystem would be wrong. Fortunately the PG philosophy is to leave what the OS does best to the OS, so its unlikely that situation would arise. However, a middle ground of being able to backup just the changes in a multi-terabyte database that have been made since the last full backup, wouldn't be controversial and is a common use case. I've read of a few attempts on the PG dev mailing list over the last couple of years, so hopefully one of them will be completed sometime soon and merged into core. Some of the work I have come across : https://github.com/ossc-db/pg_rman https://github.com/ossc-db/pg_rman https://github.com/2ndquadrant-it/barman/issues/21 https://github.com/2ndquadrant-it/barman/issues/21
randallsquared 10y ago
> Postgresql would very quickly start seeing "Postgresql requires a full time DBA, just use MariaDB" Yeah, welcome to 15 years ago, when that was the common wisdom.

elmigranto 10y ago

> incremental/differential backups What's wrong with WAL backup? Postgres can run your script and pass it path to newly created WAL chunk, that you can ship off anywhere. Or run `pg_start_backup`, rsync data directory, `pg_stop_backup`.

clg2 10y ago
You would back up the WAL as well. However there might be a lot of WAL to apply, as data gets added then deleted or changed. A differential backup (see Note) solves this somewhat for large databases by just backing up the blocks changed since the last full backup. So you might do a full backup once a week, and differentials nightly in between and also save the WAL. This means you are not backing up many TBs every night. To recover, you would restore the full backup, plus the last differential backup plus any WAL since the last differential. This reduces the volume of WAL to be applied in a recovery. Note : Differential backups would include all the changes since the last FULL backup. Incremental backups would include changes since the last backup, whether a full or the last incremental.
dgregd 10y ago
> What's wrong with WAL backup? I use PG for 10 years and I never needed incremental backups. But if someone asked me about it then I would respond that the PG has no incremental backups. So WAL backup is not well known or documented in my opinion. An average db admin needs a tool which will generate incremental backup as a single file named like dbname_20160926.sql
- MustardTiger 10y ago
  >So WAL backup is not well known or documented in my opinion What? It is the standard backup method clearly explained in the docs.
- SEJeff 10y ago
  Barman[1] gives you something better than incremental backups actually. It sets up the backup server as a database slave via PG's built-in replication. It gives you point in time recovery with the nicest CLI of any backup tool for any DB I've ever used. If you think backup tools are a minus for PG, I guess you've never used barman. Easy as pie and has saved my bacon several times when CASCADE DELETE wrecked a few dbs for me due to coworkers futzing around. [1] http://www.pgbarman.org/ http://www.pgbarman.org/

dgregd 10y ago

> why companies are still paying for commercial DBMS engines In my experience in 90% of cases application code migration to a new db engine is the real road block. I've seem many cases where even migration to a new Oracle version is a huge problem because of required app code review and full user acceptance tests. So companies pay licence fees for old unsupported versions. What is even more absurd. And frankly speaking in many cases of simple apps even SQLite has all necessary features in replace Oracle.

tajen 10y ago

> 1)"temporal" / versioned tables / time consistent views of data Oracle doesn't have transactional DDL. It means if you drop a column or create a table, it commits your transaction automatically. This is huge, because with PostgreSQL you can import a full database in a single transaction, and roll it back if anything goes wrong. You can upgrade a full application and roll back if anything goes wrong. No more partial-state headaches. Therefore I'm not sure what "versioned tables" mean. Have you tried PostgreSQL's temporal tables extension, which seems to do what you mean?

X86BSD 10y ago

3) ZFS with snapshots and send/receive are what I use for local and offsite backups. They can be as fine grained as you like time wise, and are so so so much easier to manage and restore vs traditional backups.

takeda 10y ago
This is probably easier, but it might be an issue, when you might want to skip some transactions (someone for example deletes a table). Make sure you also back up WALs.

marcosdumay 10y ago

You can very likely get any of those implemented for you by the price of a largish site license pf Oracle.