Re: Database file copy - Mailing list pgsql-hackers

From Srini Raghavan
Subject Re: Database file copy
Date
Msg-id 241137.46373.qm@web80805.mail.mud.yahoo.com
Whole thread Raw
In response to Re: Database file copy  (Alvaro Herrera <alvherre@commandprompt.com>)
List pgsql-hackers
<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/> 

pgsql-hackers by date:

Previous
From: Josh Berkus
Date:
Subject: Re: Streaming replication as a separate permissions
Next
From: Josh Berkus
Date:
Subject: Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+