Обсуждение: pg_dump
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
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
> 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
Дмитрий Воронин <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
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
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
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
>> 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
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
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
> 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
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/