Thread: 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.
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.
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
On Wed, Dec 22, 2010 at 7:35 PM, Srini Raghavan <sixersrini@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
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
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 <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
<div style="font-family:times new roman, new york, times, serif;font-size:12pt"><p>Thank you, that is a great point. <p> <p>Basedon your suggesstion, I wrote the following query:<p> <p>select * from pg_class where relisshared=true order byrelname<p> <p>The above query returns 27 rows. I evaluated the impact on the following:<p> <p>pg_auth_members - We createroles and memberships on each deploy instance, so this shouldn't be an issue.<p> <p>pg_authid - As noted in my previouspost, issuing alter and grant commands after file copy updates pg_authid with the correct information.<p> <p>pg_database- not an issue, as we are creating the database on the deploy instance, we don't copy the databaseoid over from the master instance.<p> <p>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.<p> <p>pg_pltemplate- We use plpgsql functions, and it works without any issues after file copy.<p> <p>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 appropriaterole, this system catalog gets populated correctly. I wrote this query "select oid,relname from pg_class whereoid in (select objid from pg_shdepend)" on the copied database, and it returns valid results, so this doens't seem tobe an issue. As the documentation states, currently, postgres tracks the object to role dependencies, and it may trackmore types of dependencies in the future. Role dependencies has a fix as stated above, and when new dependencies comeabout, we will need to evaluate them.<p> <p>pg_shdescription - stores optional comments, which we don't use.<p> <p>pg_tablespace- we are looking to use the default tablespace at this time, which works. Need to evaluate the impactif we need to use custom tablespace.<p> <p>The remaining entries or toast and index entries, which again should notbe an impact.<p> <p>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.<p> <p>Thanks<p> <p>Srini<p> <p> </div><br/>
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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 <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Fri, Jan 14, 2011 at 9:13 AM, Bruce Momjian <bruce@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
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 <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> wrote: > Excerpts from Robert Haas's message: > >> I'd rather remove the deprecating warning. > > +1 +1 -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Alvaro Herrera <alvherre@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
Tom Lane <tgl@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
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@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
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@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
"Kevin Grittner" <Kevin.Grittner@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
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@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
Tom Lane wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > > Tom Lane <tgl@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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Fri, Jan 14, 2011 at 2:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@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
Robert Haas <robertmhaas@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
On Fri, Jan 14, 2011 at 3:41 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Robert Haas <robertmhaas@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
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 <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Alvaro Herrera <alvherre@commandprompt.com>; Bruce Momjian <bruce@momjian.us>; pgsql-hackers <pgsql-hackers@postgresql.org>; Srini Raghavan <sixersrini@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 <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@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
Kevin Grittner wrote: > Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Kevin Grittner" <Kevin.Grittner@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 <bruce@momjian.us> http://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 <productname>PostgreSQL</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 + <productname>PostgreSQL</productname> 9.0; the unparenthesized + syntax is deprecated. </para> </refsect1> @@ -102,8 +102,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER"> Specifying <literal>FREEZE</literal> is equivalent to performing <command>VACUUM</command> with the <xref linkend="guc-vacuum-freeze-min-age"> parameter - set to zero. The <literal>FREEZE</literal> option is deprecated and - will be removed in a future release; set the parameter instead. + set to zero. </para> </listitem> </varlistentry>