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