Thread

Database file copy

Srini Raghavan <sixersrini@yahoo.com> — 2010-12-23T00:35:12Z

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

Re: Database file copy

Robert Haas <robertmhaas@gmail.com> — 2010-12-23T16:04:26Z

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

Re: Database file copy

Srini Raghavan <sixersrini@yahoo.com> — 2010-12-23T21:55:20Z

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: Database file copy

Alvaro Herrera <alvherre@commandprompt.com> — 2010-12-23T22:18:36Z

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

Re: Database file copy

Srini Raghavan <sixersrini@yahoo.com> — 2010-12-23T23:18:01Z

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

Re: Database file copy

Bruce Momjian <bruce@momjian.us> — 2011-01-13T03:05:53Z

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. +

Re: Database file copy

Alvaro Herrera <alvherre@commandprompt.com> — 2011-01-13T20:18:02Z

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

Re: Database file copy

Bruce Momjian <bruce@momjian.us> — 2011-01-14T14:13:46Z

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. +

Re: Database file copy

Robert Haas <robertmhaas@gmail.com> — 2011-01-14T14:18:16Z

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

Re: Database file copy

Alvaro Herrera <alvherre@commandprompt.com> — 2011-01-14T14:26:05Z

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

Re: Database file copy

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-01-14T14:44:31Z

Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Excerpts from Robert Haas's message:
> 
>> I'd rather remove the deprecating warning.
> 
> +1
 
+1
 
-Kevin

Re: Database file copy

Tom Lane <tgl@sss.pgh.pa.us> — 2011-01-14T17:17:46Z

"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

Re: Database file copy

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-01-14T17:39:47Z

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

Re: Database file copy

Tom Lane <tgl@sss.pgh.pa.us> — 2011-01-14T18:03:22Z

"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

Re: Database file copy

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-01-14T18:13:32Z

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

Re: Database file copy

Tom Lane <tgl@sss.pgh.pa.us> — 2011-01-14T19:15:58Z

"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

Re: Database file copy

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-01-14T19:49:29Z

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

Re: Database file copy

Bruce Momjian <bruce@momjian.us> — 2011-01-14T20:11:29Z

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. +

Re: Database file copy

Robert Haas <robertmhaas@gmail.com> — 2011-01-14T20:36:02Z

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

Re: Database file copy

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-01-14T20:41:13Z

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

Re: Database file copy

Robert Haas <robertmhaas@gmail.com> — 2011-01-14T20:43:36Z

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

Re: Database file copy

Srini Raghavan <sixersrini@yahoo.com> — 2011-01-14T20:59:29Z

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

Re: Database file copy

Bruce Momjian <bruce@momjian.us> — 2011-03-11T10:34:53Z

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. +