Re: 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

On Wed, Dec 22, 2010 at 7:35 PM, Srini Raghavan <sixersrini(at)yahoo(dot)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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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(at)momjian(dot)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(at)commandprompt(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Robert Haas's message:
>
>> I'd rather remove the deprecating warning.
>
> +1

+1

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)sss(dot)pgh(dot)pa(dot)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(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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(dot)Grittner(at)wicourts(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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(dot)Grittner(at)wicourts(dot)gov> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)momjian(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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(at)gmail(dot)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(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>; Alvaro Herrera
<alvherre(at)commandprompt(dot)com>; Bruce Momjian <bruce(at)momjian(dot)us>; pgsql-hackers
<pgsql-hackers(at)postgresql(dot)org>; Srini Raghavan <sixersrini(at)yahoo(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +