Thread: pg_dump using anything other than custom and directory

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

-- 
Angular momentum makes the world go 'round.

Setting up test environments. 

On Fri, Apr 12, 2019 at 3:10 PM Ron <ronljohnsonjr@gmail.com> wrote:

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

You can hand edit raw text. 

On Fri, Apr 12, 2019 at 5:32 PM Ron <ronljohnsonjr@gmail.com> wrote:

What makes -Ft and -Fp so much better than -Fc and -Fd at setting up test environments?

On 4/12/19 3:50 PM, Derek Viljoen wrote:

Setting up test environments. 

On Fri, Apr 12, 2019 at 3:10 PM Ron <ronljohnsonjr@gmail.com> wrote:

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

Ron <ronljohnsonjr@gmail.com> writes:
> 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?

The historical argument for the tar format is that you can get your
data out of it with a standard Unix tool (tar, of course), rather than
having to depend on the availability of pg_restore.  Certainly there's
room to argue about how important that really is, but I don't think
the validity of the argument is much different than it was in 2001.

You need to be able to get a plain-text dump if you want to edit
the data or schema at all, which is a pretty common requirement.
However, as long as you're willing to assume the availability of
pg_restore, you can extract plain text from one of the other formats;
so this point isn't a reason not to make your dump in one of the
other formats to begin with.

            regards, tom lane

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