References: <93bf4376-6167-a89f-83c5-36aa0dadae2e@gmail.com>
User-agent: mu4e 1.1.0; emacs 26.1
From: Tim Cross <theophilusx@gmail.com>
To: pgsql-admin@lists.postgresql.org
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg_dump using anything other than custom and directory
In-reply-to: <93bf4376-6167-a89f-83c5-36aa0dadae2e@gmail.com>
Date: Sat, 13 Apr 2019 10:44:38 +1000
Message-ID: <87ef66vimh.fsf@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk


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@gmail.com> 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