pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump using anything other than custom and directory
8+ messages / 4 participants
[nested] [flat]
* pg_dump using anything other than custom and directory
@ 2019-04-12 20:10 Ron <ronljohnsonjr@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Ron @ 2019-04-12 20:10 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump using anything other than custom and directory
@ 2019-04-12 20:50 Derek Viljoen <derekv@infinite.io>
parent: Ron <ronljohnsonjr@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Derek Viljoen @ 2019-04-12 20:50 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
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
>
> --
> Angular momentum makes the world go 'round.
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump using anything other than custom and directory
@ 2019-04-12 22:32 Ron <ronljohnsonjr@gmail.com>
parent: Derek Viljoen <derekv@infinite.io>
0 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2019-04-12 22:32 UTC (permalink / raw)
To: pgsql-admin
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
> <mailto: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
>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump using anything other than custom and directory
@ 2019-04-12 22:34 Derek Viljoen <derekv@infinite.io>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Derek Viljoen @ 2019-04-12 22:34 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
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
>>
>
>
> --
> Angular momentum makes the world go 'round.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump using anything other than custom and directory
@ 2019-04-12 23:02 Ron <ronljohnsonjr@gmail.com>
parent: Derek Viljoen <derekv@infinite.io>
0 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2019-04-12 23:02 UTC (permalink / raw)
To: pgsql-admin
I just hand-edited a .dat file from an -Fd backup.
On 4/12/19 5:34 PM, Derek Viljoen wrote:
> You can hand edit raw text.
>
> On Fri, Apr 12, 2019 at 5:32 PM Ron <ronljohnsonjr@gmail.com
> <mailto: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
>> <mailto: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
>>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump using anything other than custom and directory
@ 2019-04-12 23:04 Derek Viljoen <derekv@infinite.io>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Derek Viljoen @ 2019-04-12 23:04 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
Glad that works for you.
On Fri, Apr 12, 2019 at 6:02 PM Ron <ronljohnsonjr@gmail.com> wrote:
> I just hand-edited a .dat file from an -Fd backup.
>
>
> On 4/12/19 5:34 PM, Derek Viljoen wrote:
>
> 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
>>>
>>
> --
> Angular momentum makes the world go 'round.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump using anything other than custom and directory
@ 2019-04-12 23:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ron <ronljohnsonjr@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2019-04-12 23:05 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump using anything other than custom and directory
@ 2019-04-13 00:44 Tim Cross <theophilusx@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Tim Cross @ 2019-04-13 00:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2019-04-13 00:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-04-12 20:10 pg_dump using anything other than custom and directory Ron <ronljohnsonjr@gmail.com>
2019-04-12 20:50 ` Derek Viljoen <derekv@infinite.io>
2019-04-12 22:32 ` Ron <ronljohnsonjr@gmail.com>
2019-04-12 22:34 ` Derek Viljoen <derekv@infinite.io>
2019-04-12 23:02 ` Ron <ronljohnsonjr@gmail.com>
2019-04-12 23:04 ` Derek Viljoen <derekv@infinite.io>
2019-04-12 23:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-04-13 00:44 ` Tim Cross <theophilusx@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox