Re: [HACKERS] Database file copy
Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: shouldn't we be getting support for the new syntax added, so there can be a release or two supporting both? You mean like 9.0? Yeah, just like that. If we're going to be supporting that long term, we should probably change the note about FREEZE being deprecated, though. So, still +1 on removing the wording about FREEZE being deprecated, but instead we should mention what actually *is* deprecated (the omission of the parentheses). Done with the attached, applied patch. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index dee1cc3..5b5b161 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -70,9 +70,9 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ replaceable class=PARAMETER When the option list is surrounded by parentheses, the options can be written in any order. Without parentheses, options must be specified in exactly the order shown above. - Prior to productnamePostgreSQL/productname 9.0, the unparenthesized - syntax was the only one supported. It is expected that all new options - will be supported only in the parenthesized syntax. + The unparenthesized syntax was added in + productnamePostgreSQL/productname 9.0; the unparenthesized + syntax is deprecated. /para /refsect1 @@ -102,8 +102,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ replaceable class=PARAMETER Specifying literalFREEZE/literal is equivalent to performing commandVACUUM/command with the xref linkend=guc-vacuum-freeze-min-age parameter - set to zero. The literalFREEZE/literal option is deprecated and - will be removed in a future release; set the parameter instead. + set to zero. /para /listitem /varlistentry -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of jue ene 13 00:05:53 -0300 2011: Srini Raghavan wrote: Thank you very much for reviewing, appreciate the feedback.? As pointed out by you, it is always best to test it out with the latest version, so, I tested the same approach with postgres 9.0.2 on windows just now, and it works! I forgot to mention earlier that in addition to setting vacuum_freeze_table_age to 0, vacuum_freeze_min_age must also be set to 0 to reset xmin with the FrozenXid. I wonder if you should be using VACUUM FREEZE instead of having to set variables. The documentation says you shouldn't: FREEZE Selects aggressive freezing of tuples. Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age parameter set to zero. The FREEZE option is deprecated and will be removed in a future release; set the parameter instead. http://www.postgresql.org/docs/9.0/static/sql-vacuum.html I didn't know that. I added the -z(freeze) option to vacuumdb in 8.4 for use by pg_upgrade. I think the original idea was that people should never need to freeze anything, but it turns out pg_upgrade and this user need it so maybe depricating is not a good idea. I guess pg_upgrade could call vacuumdb with a PGOPTIONS flag to force a vacuum_freeze_min_age value. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
On Fri, Jan 14, 2011 at 9:13 AM, Bruce Momjian br...@momjian.us wrote: Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of jue ene 13 00:05:53 -0300 2011: Srini Raghavan wrote: Thank you very much for reviewing, appreciate the feedback.? As pointed out by you, it is always best to test it out with the latest version, so, I tested the same approach with postgres 9.0.2 on windows just now, and it works! I forgot to mention earlier that in addition to setting vacuum_freeze_table_age to 0, vacuum_freeze_min_age must also be set to 0 to reset xmin with the FrozenXid. I wonder if you should be using VACUUM FREEZE instead of having to set variables. The documentation says you shouldn't: FREEZE Selects aggressive freezing of tuples. Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age parameter set to zero. The FREEZE option is deprecated and will be removed in a future release; set the parameter instead. http://www.postgresql.org/docs/9.0/static/sql-vacuum.html I didn't know that. I added the -z(freeze) option to vacuumdb in 8.4 for use by pg_upgrade. I think the original idea was that people should never need to freeze anything, but it turns out pg_upgrade and this user need it so maybe depricating is not a good idea. I guess pg_upgrade could call vacuumdb with a PGOPTIONS flag to force a vacuum_freeze_min_age value. I'd rather remove the deprecating warning. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Excerpts from Robert Haas's message of vie ene 14 11:18:16 -0300 2011: I'd rather remove the deprecating warning. +1 -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message: I'd rather remove the deprecating warning. +1 +1 -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Kevin Grittner kevin.gritt...@wicourts.gov writes: Alvaro Herrera alvhe...@commandprompt.com wrote: I'd rather remove the deprecating warning. +1 +1 The reason for wanting to deprecate and ultimately remove that syntax is so we can get rid of FREEZE as a reserved word. We could probably still allow the new-style syntax VACUUM (FREEZE) ... but VACUUM FREEZE really needs to be killed. pg_upgrade is NOT a good reason to have a nonstandard reserved word in the grammar. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Tom Lane t...@sss.pgh.pa.us wrote: The reason for wanting to deprecate and ultimately remove that syntax is so we can get rid of FREEZE as a reserved word. We could probably still allow the new-style syntax VACUUM (FREEZE) Oh, OK. I can go along with that. If we're going that route, though, shouldn't we be getting support for the new syntax added, so there can be a release or two supporting both? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: The reason for wanting to deprecate and ultimately remove that syntax is so we can get rid of FREEZE as a reserved word. Oh, OK. I can go along with that. If we're going that route, though, shouldn't we be getting support for the new syntax added, so there can be a release or two supporting both? You mean like 9.0? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: shouldn't we be getting support for the new syntax added, so there can be a release or two supporting both? You mean like 9.0? Yeah, just like that. If we're going to be supporting that long term, we should probably change the note about FREEZE being deprecated, though. So, still +1 on removing the wording about FREEZE being deprecated, but instead we should mention what actually *is* deprecated (the omission of the parentheses). -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Kevin Grittner kevin.gritt...@wicourts.gov writes: If we're going to be supporting that long term, we should probably change the note about FREEZE being deprecated, though. So, still +1 on removing the wording about FREEZE being deprecated, but instead we should mention what actually *is* deprecated (the omission of the parentheses). If we're going to do that, we should deprecate the unparenthesized syntax altogether, with an eye to de-reserving VERBOSE and ANALYZE as well. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: So, still +1 on removing the wording about FREEZE being deprecated, but instead we should mention what actually *is* deprecated (the omission of the parentheses). If we're going to do that, we should deprecate the unparenthesized syntax altogether, with an eye to de-reserving VERBOSE and ANALYZE as well. +1 -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Tom Lane wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: The reason for wanting to deprecate and ultimately remove that syntax is so we can get rid of FREEZE as a reserved word. Oh, OK. I can go along with that. If we're going that route, though, shouldn't we be getting support for the new syntax added, so there can be a release or two supporting both? You mean like 9.0? FYI, I just checked and pg_upgrade does not run the VACUUM command at all, but vacuumdb, and vacuumdb knows to use parentheses when connecting to a = 9.0 server. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
On Fri, Jan 14, 2011 at 2:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: If we're going to be supporting that long term, we should probably change the note about FREEZE being deprecated, though. So, still +1 on removing the wording about FREEZE being deprecated, but instead we should mention what actually *is* deprecated (the omission of the parentheses). If we're going to do that, we should deprecate the unparenthesized syntax altogether, with an eye to de-reserving VERBOSE and ANALYZE as well. I'm not wildly enthusiastic about breaking this with only one intervening release. We normally support deprecated syntax for quite a bit longer than that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Robert Haas robertmh...@gmail.com wrote: I'm not wildly enthusiastic about breaking this with only one intervening release. We normally support deprecated syntax for quite a bit longer than that. one intervening release? Where did you see that? I thought we were just talking about deprecating the old syntax, not breaking it. If history is any guide, getting the deprecation mentioned in the docs now would lead to actual removal somewhere around 10.0. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
On Fri, Jan 14, 2011 at 3:41 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: I'm not wildly enthusiastic about breaking this with only one intervening release. We normally support deprecated syntax for quite a bit longer than that. one intervening release? Where did you see that? I thought we were just talking about deprecating the old syntax, not breaking it. If history is any guide, getting the deprecation mentioned in the docs now would lead to actual removal somewhere around 10.0. Oh, I guess I'm confused then... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Thanks for considering our special scenario. I did not use the vacuum freeze option because the documentation said it is going to be deprecrated. Based on the positive votes so far, I gather that a vacuum (freeze) syntax will be supported in some version in the future, until then, I can continue to use the existing vacuum freeze syntax? I did try it and it works. Thank you, Srini From: Robert Haas robertmh...@gmail.com To: Tom Lane t...@sss.pgh.pa.us Cc: Kevin Grittner kevin.gritt...@wicourts.gov; Alvaro Herrera alvhe...@commandprompt.com; Bruce Momjian br...@momjian.us; pgsql-hackers pgsql-hackers@postgresql.org; Srini Raghavan sixersr...@yahoo.com Sent: Fri, January 14, 2011 3:36:02 PM Subject: Re: [HACKERS] Database file copy On Fri, Jan 14, 2011 at 2:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: If we're going to be supporting that long term, we should probably change the note about FREEZE being deprecated, though. So, still +1 on removing the wording about FREEZE being deprecated, but instead we should mention what actually *is* deprecated (the omission of the parentheses). If we're going to do that, we should deprecate the unparenthesized syntax altogether, with an eye to de-reserving VERBOSE and ANALYZE as well. I'm not wildly enthusiastic about breaking this with only one intervening release. We normally support deprecated syntax for quite a bit longer than that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Database file copy
Excerpts from Bruce Momjian's message of jue ene 13 00:05:53 -0300 2011: Srini Raghavan wrote: Thank you very much for reviewing, appreciate the feedback.? As pointed out by you, it is always best to test it out with the latest version, so, I tested the same approach with postgres 9.0.2 on windows just now, and it works! I forgot to mention earlier that in addition to setting vacuum_freeze_table_age to 0, vacuum_freeze_min_age must also be set to 0 to reset xmin with the FrozenXid. I wonder if you should be using VACUUM FREEZE instead of having to set variables. The documentation says you shouldn't: FREEZE Selects aggressive freezing of tuples. Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age parameter set to zero. The FREEZE option is deprecated and will be removed in a future release; set the parameter instead. http://www.postgresql.org/docs/9.0/static/sql-vacuum.html -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Srini Raghavan wrote: Thank you very much for reviewing, appreciate the feedback.? As pointed out by you, it is always best to test it out with the latest version, so, I tested the same approach with postgres 9.0.2 on windows just now, and it works! I forgot to mention earlier that in addition to setting vacuum_freeze_table_age to 0, vacuum_freeze_min_age must also be set to 0 to reset xmin with the FrozenXid. I wonder if you should be using VACUUM FREEZE instead of having to set variables. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
On Wed, Dec 22, 2010 at 7:35 PM, Srini Raghavan sixersr...@yahoo.com wrote: I have tested this and it works, and I am continuing to test it more. I would like for validation of this idea from the experts and the community to make sure I haven't overlooked something obvious that might cause issues. Interesting idea. It seems like it might be possible to make this work. One obvious thing to watch out for is object ownership information. Roles are stored in pg_authid, which is a shared catalog, so if you're unlucky you could manage to create a database containing one or more objects that owned by a role ID that doesn't exist in pg_authid, which will probably break things all over the place. There could be other pitfalls as well but that's the only one that's obvious to me off the top of my head... I would strongly recommend basing this on the latest minor release of PostgreSQL 9.0 rather than an outdated minor release of PostgreSQL 8.4. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Thank you very much for reviewing, appreciate the feedback. As pointed out by you, it is always best to test it out with the latest version, so, I tested the same approach with postgres 9.0.2 on windows just now, and it works! I forgot to mention earlier that in addition to setting vacuum_freeze_table_age to 0, vacuum_freeze_min_age must also be set to 0 to reset xmin with the FrozenXid. And you were spot on with regards to permission issues with roles. I had been testing with the postgres account, which is a superuser and it always works. After the database files are copied over in the deploy instance, any object that had ownership set to a custom role gets messed up, and logging in as that user gives permission denined error. But, there is a easy fix to this. As the postgres user, I ran the alter table objectname owner to rolename command for every object, followed by grant all on objecttype objectname to rolename command for every object, which resolved the permission denied issue. Thanks for pointing this out. Please let me know if you or anyone think of any other potential issues. Thanks again for reviewing. Srini
Re: [HACKERS] Database file copy
Excerpts from Srini Raghavan's message of jue dic 23 18:55:20 -0300 2010: Please let me know if you or anyone think of any other potential issues. Thanks again for reviewing. I think anything in the shared catalogs could be an issue (look for tables with pg_class.relisshared=true). I think you'll need to do something about shared dependencies as well; not sure what. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database file copy
Thank you, that is a great point. Based on your suggesstion, I wrote the following query: select * from pg_class where relisshared=true order by relname The above query returns 27 rows. I evaluated the impact on the following: pg_auth_members - We create roles and memberships on each deploy instance, so this shouldn't be an issue. pg_authid - As noted in my previous post, issuing alter and grant commands after file copy updates pg_authid with the correct information. pg_database - not an issue, as we are creating the database on the deploy instance, we don't copy the database oid over from the master instance. pg_db_role_setting - We don't have any database specific role settings. Even if we have a need in the future, we will set this up on the deploy instance, so, this shouldn't be an issue. pg_pltemplate - We use plpgsql functions, and it works without any issues after file copy. pg_shdepend - There is one SHARED_DEPENDENCY_PIN(p) entry in this system catalog, and the remaining are SHARED_DEPENDENCY_OWNER (o) entries. Since I am issuing an alter command to change the ownership after file copy to the appropriate role, this system catalog gets populated correctly. I wrote this query select oid,relname from pg_class where oid in (select objid from pg_shdepend) on the copied database, and it returns valid results, so this doens't seem to be an issue. As the documentation states, currently, postgres tracks the object to role dependencies, and it may track more types of dependencies in the future. Role dependencies has a fix as stated above, and when new dependencies come about, we will need to evaluate them. pg_shdescription - stores optional comments, which we don't use. pg_tablespace - we are looking to use the default tablespace at this time, which works. Need to evaluate the impact if we need to use custom tablespace. The remaining entries or toast and index entries, which again should not be an impact. Anything else? I am feeling confident about this after each review post. And, whereever I have said this shouldn't be an issue above, if you see any discrepancies, kindly highlight. Thanks Srini
[HACKERS] Database file copy
Hello, [Tried the general forum, didn't hear from anyone so far, trying this forum now, please review, thanks] We are looking to distribute postgres databases to our customers along with our application. We are currently evaluating postgres version 8.4.4. The database can be of size 25 gb (compressed files fits in few dvds, the product is distributed on dvds). The pg_restore of this database takes several hours on the low end machines running windows os. The pg_restore is run during our product install, and the current install time projection is not acceptable. Our customers can purchase different databases over a period of time, and the application makes transactional updates to the databases after installation. Hence, copying the entire data folder instead of using the pg_restore is not an option, as the transactional updates will be lost. I have read the documentation and the few posts available that discourages file copy based restore of individual databases, but, I have found a way to do this. I would appreciate if the experts can read and advise if the approach will work, given our environment and usage boundaries. Master Postgres instance (this is where we create the data, we have complete control of this environment): 1. Create the database and populate data. 2. Set vacuum_freeze_table_age to 0 in the postgresql.conf 3. Run vacuum full - this will reset the row xid to the FrozenXid 4. Shutdown postgres and take a copy of the files for the given database. In the deploy instance at the customer site: 1. Create the new database. 2. Shutdown postgres instance and copy the database files created in the master instance to the database specific folder. 3. Start postgres instance. We don't use table row oids. If the cluster wide oid collides with the oid in the copied database files during subsequent ddl operations, postgres resolves this by skipping to the next available oid. There will be a delay to find the next available oid, which is acceptable in our case, as the ddl operations at the customer site are rare. And, the vacuum full with vacuum_freeze_table_age set to 0 on the master instance takes care of the xmin, allowing transactions to be visible, and for further transactions at the customer site to continue without colliding. I have tested this and it works, and I am continuing to test it more. I would like for validation of this idea from the experts and the community to make sure I haven't overlooked something obvious that might cause issues. Thank you, Srini
