Thread: pg_dump

pg_dump

From
Dmitry Voronin
Date:
Hello, guys.

I have a database testdb with commet 'test comment' and security label 'classified'. I create dump by pg_dump:

pg_dump -h 127.0.0.1 -d testdb -U postgres --format c dump

So, I want to restore a dump with comment and security label to testdb_restore. I run:

pg_restore -h 127.0.0.1 -d testdb_restore dump

So, we have, that SECURITY LABEL and COMMENT will be applied to database testdb but not testdb_restore. I think, that
it'snot good.
 

-- 
Best regards, Dmitry Voronin



Re: pg_dump

From
Noah Misch
Date:
On Wed, Oct 28, 2015 at 10:44:37AM +0300, Dmitry Voronin wrote:
> I have a database testdb with commet 'test comment' and security label 'classified'. I create dump by pg_dump:
> 
> pg_dump -h 127.0.0.1 -d testdb -U postgres --format c dump
> 
> So, I want to restore a dump with comment and security label to testdb_restore. I run:
> 
> pg_restore -h 127.0.0.1 -d testdb_restore dump
> 
> So, we have, that SECURITY LABEL and COMMENT will be applied to database testdb but not testdb_restore. I think, that
it'snot good.
 

It's a problem.  See this recent discussion:
http://www.postgresql.org/message-id/flat/20150710115735.GH26521@alap3.anarazel.de



Re: pg_dump

From
Дмитрий Воронин
Date:
>  It's a problem. See this recent discussion:
>  http://www.postgresql.org/message-id/flat/20150710115735.GH26521@alap3.anarazel.de

Postgresmen, we have a SQL function "current_database", which can be called by statement "SELECT CURRENT_CATALOG".

If we will use CURRENT_CATALOG keyword, we can update syntax of COMMENT statement:

COMMENT ON DATABASE CURRENT_CATALOG IS 'comment';

And pg_dump will create this line for database. What are you think about this idea? Later, I send a patch with it.

P.S. Maybe we can apply this mechanism for dumping SECURITY LABEL statetment.


-- 
Best regards, Dmitry Voronin



Re: pg_dump

From
Tom Lane
Date:
Дмитрий Воронин <carriingfate92@yandex.ru> writes:
>> �It's a problem. See this recent discussion:
>> �http://www.postgresql.org/message-id/flat/20150710115735.GH26521@alap3.anarazel.de

> Postgresmen, we have a SQL function "current_database", which can be called by statement "SELECT CURRENT_CATALOG".

> If we will use CURRENT_CATALOG keyword, we can update syntax of COMMENT statement:

> COMMENT ON DATABASE CURRENT_CATALOG IS 'comment';

> And pg_dump will create this line for database. What are you think about this idea?

We don't need hasty patches.  What we need is a re-think of the division
of labor between pg_dump and pg_dumpall.  Up to now, pg_dump has only been
charged with dumping/restoring the data "inside" an individual database,
not with handling any database-level properties.  Those are the
responsibility of pg_dumpall.

I'd be the first to agree that maybe this wasn't the best design, but at
least it's consistent.  If we're going to change things, we need to start
by deciding where we're going to re-draw the line, and figuring out what
sort of impact that will have in terms of compatibility considerations
and users' backup/restore procedures.
        regards, tom lane



Re: pg_dump

From
David Fetter
Date:
On Thu, Oct 29, 2015 at 10:51:20AM -0400, Tom Lane wrote:
> Дмитрий Воронин <carriingfate92@yandex.ru> writes:
> >> �It's a problem. See this recent discussion:
> >> �http://www.postgresql.org/message-id/flat/20150710115735.GH26521@alap3.anarazel.de
> 
> > Postgresmen, we have a SQL function "current_database", which can be called by statement "SELECT CURRENT_CATALOG".
> 
> > If we will use CURRENT_CATALOG keyword, we can update syntax of COMMENT statement:
> 
> > COMMENT ON DATABASE CURRENT_CATALOG IS 'comment';
> 
> > And pg_dump will create this line for database. What are you think about this idea?
> 
> We don't need hasty patches.  What we need is a re-think of the division
> of labor between pg_dump and pg_dumpall.  Up to now, pg_dump has only been
> charged with dumping/restoring the data "inside" an individual database,
> not with handling any database-level properties.  Those are the
> responsibility of pg_dumpall.
> 
> I'd be the first to agree that maybe this wasn't the best design, but at
> least it's consistent.  If we're going to change things, we need to start
> by deciding where we're going to re-draw the line, and figuring out what
> sort of impact that will have in terms of compatibility considerations
> and users' backup/restore procedures.

In this vein, I'd like humbly to suggest that we draw the line in a
way that finishes the already accomplished work of excluding
pg_dumpall entirely.  We should probably leave a pg_dumpall-compatible
wrapper for pg_dump for a few versions, but not if doing so cramps
development.

What pg_dumpall now still does that pg_dump doesn't is, as far as I
know:

- Whole-instance dumping (its original purpose, as far as I can tell)
- Auth (roles/secrets)
- Tablespaces

I believe that a relatively short patch to pg_dump would allow us to
make better versions of at least the first two.  By better, I mean,

- Not require that a whole-instance dump be in a single file, and
- Be able to dump only the auth stuff relevant to the specified DB objects.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate



Re: pg_dump

From
Дмитрий Воронин
Date:
David, do you want to have one dumper program for postgres?

Maybe it will be a good idea to make a dumper with some dumping levels:
- all cluster
- global objects
- database level



Re: pg_dump

From
David Fetter
Date:
On Thu, Oct 29, 2015 at 06:37:46PM +0300, Дмитрий Воронин wrote:
> David, do you want to have one dumper program for postgres?

Yes, and pg_dump appears to be the best candidate for evolution into
one.

That there are two separate ones is the result of design decisions
that may very well have made sense at the time they were made, but no
longer do.

> Maybe it will be a good idea to make a dumper with some dumping levels:
> - all cluster
> - global objects
> - database level

I'm not sure I understand.  Is there some utility in dividing this
into these particular levels?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate



Re: pg_dump

From
Дмитрий Воронин
Date:
>>  Maybe it will be a good idea to make a dumper with some dumping levels:
>>  - all cluster
>>  - global objects
>>  - database level
>
> I'm not sure I understand. Is there some utility in dividing this
> into these particular levels?

-all cluster -- it's what pg_dumpall and pg_dump do.
- global objects -- it's dumping tablespaces, roles, database(structure).
- database level -- it's dumping all inside one database.

-- 
Best regards, Dmitry Voronin



Re: pg_dump

From
David Fetter
Date:
On Thu, Oct 29, 2015 at 07:03:12PM +0300, Дмитрий Воронин wrote:
> 
> >>  Maybe it will be a good idea to make a dumper with some dumping levels:
> >>  - all cluster
> >>  - global objects
> >>  - database level
> >
> > I'm not sure I understand. Is there some utility in dividing this
> > into these particular levels?
> 
> -all cluster -- it's what pg_dumpall and pg_dump do.
> - global objects -- it's dumping tablespaces, roles, database(structure).
> - database level -- it's dumping all inside one database.

More specifically, I am not quite understanding the distinction
between "all cluster" and "global objects."

What do you have in mind on the implementation side?  Do you think
pg_dump is a suitable baseline, or were you thinking of something
different, and if so, what?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate



Re: pg_dump

From
Jeff Janes
Date:
On Thu, Oct 29, 2015 at 7:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Дмитрий Воронин <carriingfate92@yandex.ru> writes:
>>> šIt's a problem. See this recent discussion:
>>> šhttp://www.postgresql.org/message-id/flat/20150710115735.GH26521@alap3.anarazel.de
>
>> Postgresmen, we have a SQL function "current_database", which can be called by statement "SELECT CURRENT_CATALOG".
>
>> If we will use CURRENT_CATALOG keyword, we can update syntax of COMMENT statement:
>
>> COMMENT ON DATABASE CURRENT_CATALOG IS 'comment';
>
>> And pg_dump will create this line for database. What are you think about this idea?
>
> We don't need hasty patches.  What we need is a re-think of the division
> of labor between pg_dump and pg_dumpall.  Up to now, pg_dump has only been
> charged with dumping/restoring the data "inside" an individual database,
> not with handling any database-level properties.

I don't understand this comment.  The whole point of the thread is
that pg_dump (with -C or -Fc) is already dumping this database-level
information, but in a way that doesn't reload if the database name has
changed.  The info is already there, and at least in the case of
COMMENT it has been for a long time.

Cheers,

Jeff



Re: pg_dump

From
Дмитрий Воронин
Date:
> More specifically, I am not quite understanding the distinction
> between "all cluster" and "global objects."

all cluster is roles, tablespaces, databases with it's content.
global objects is roles, tablespaces.

> What do you have in mind on the implementation side? Do you think
> pg_dump is a suitable baseline, or were you thinking of something
> different, and if so, what?

I think, the baseline is pg_dump. So, pg_dump create a dump of database and it's content. pg_dump must backup comments,
securitylabels (if exists)
 
in some portable format (see my messages earlier). In our solution we now use proposed way for backup and restore
COMMENTsand SECURITY LABELs on DATABASE).
 

If my solution is good, I am ready to cooperate with rethinking and rewriting (if needed) mechanism of dumping in
PostgreSQL.

P.S. I already think so, that we needed in rethinking idea of dumping and restore objects if PostgreSQL.

Thank you.

-- 
Best regards, Dmitry Voronin



Re: pg_dump

From
rafael
Date:

On 10/29/2015 03:51 PM, Tom Lane wrote:
> We don't need hasty patches.  What we need is a re-think of the division
> of labor between pg_dump and pg_dumpall.  Up to now, pg_dump has only been
> charged with dumping/restoring the data "inside" an individual database,
> not with handling any database-level properties.  Those are the
> responsibility of pg_dumpall.
> 

Hello

A wiki page with some proposals to improve pg_dump can be found here:
https://wiki.postgresql.org/wiki/Pg_dump_improvements

It was created sometime ago after a discussion on pgsql-hackers. Refs on
the wikipage.

regards
-- 
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/