Saturday, December 5, 2009

discovering namespaces in postgresql

I've been working on a project with a friend. We're using PostgreSQL for our db...

I thoughtfully prepared the DB locally. I then did a complete dump of it and gave it to my chum...

However, I hadn't anticipated a few things:
  1. I didn't tell my friend the username I had used when creating the database. The PostgreSQL dump had helpfully included a bunch of 'ALTER TABLE, OWNER TO' commands. If you haven't anticipated this - and the user doesn't exist - you will end up with a bunch of problems when ingesting the dump.
    Proposed solutions:

    1. Create a user with the name recorded in the dump file before ingestion
    2. when doing a pg_dump, add the --no-owner flag.

  2.  I didn't tell my friend that I have used schema in the db. Once they had got over the first problem, they were greeted with an apprently empty database! If you don't set your search_path to add the relevant schema names, \dt will tell you 'no relations found'
    Proposed solutions:

    1. Tell my friend the names of the schema that i've used - maybe even in the form of a SET search_path=newschema,"$user",public; command.
    2. Let my friend grep the dump file for 'search_path'.
    3. Let my friend figure out the relevant names of the schema by having them do a SELECT * FROM pg_namespaces;

I think I could improve on the namespace discovery query, possibably by just suggesting that my friend does a SELECT * FROM pg_tables; which seems to reveal more useful information.

UPDATE: after some more playing around, I've discovered that I'll will save myself alot of time by editing the dump file to remove all the 'alter to' commands. One it is ingested, the resulting db requires alot of effort to manipulate all the permissions.

No comments: