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