I use the plan format quite often for data migration tasks. For example, I can run the dump through sed and change all references to a database/table/column/etc or modify data, such as replacing email addresses with test addresses. I've used this technique to have scripts which I can use to refresh development or uat environments from production systems when other alternatives, like file system snapshots are not available or we need to modify the data. A similar technique can be used to mask/change sensitive data, though there are probably better tools for doing that. The tar format can be quite useful, particularly when you use it in a data processing 'pipeline' i.e. data > tar > process (possibly with network channels) > tar > new data Having the ability to dump out the data in a form which can be manipulated by other tools is extremely useful. Sometimes you need/want to do something which simply has not been catered for using the other tools - having a plain text dump provides that flexibility. Yes, they can be large and for Tb size DBs are probably impracticable, but there are a lot of important or business critical DBs which are quite small in size (it isn't the size of the database, but how you use it!). You can also avoid version compatibility issues. We had one of these recently. An external developer wanted a recent dump of a database to work on locally. His local PG versions were quite old and he didn't want to update/upgrade. When trying to use pg_restore, he got a header version mismatch error and could not import the data. A gzipped plain dump of the database was not much larger than the custom dump and he was able to run it in psql with no problems. (yes, he should upgrade/update his PG install, but that is a different battle and not one to have at a mission critical time). Tim Ron <ronljohnsonjr@xxxxxxxxx> writes: > Hi, > > In 2019 using supported versions of PostgreSQL, what practical use is there to > use the tar format, and -- other than migrating trivially sized databases to > other RDBMSs -- the plain format? > > Thanks -- Tim Cross
