1

I want to be able to clone the contents of our postgre production database to an ownerless local database efficiently. I've successfully done this, but it was a laborious process with the following steps

$ pg_dump [prod_db] > tempfile

[Go through tempfile manually removing all 60ish references to the owner, named 'postgres']

$ cat tempfile > psql [local_db]

Otherwise when I ran the last step, I got a bunch of SQL error messages saying ERROR: role "postgres" does not exist. I tried recreating the local db with a matching 'postgres' owner, but a) I still got the same type of errors, and b) I don't want to have an owner set for my local database if it means I'll have to log into it.

Is there a best practice/efficient way of doing this if I want to re-clone it in future?

Arepo
  • 825
  • 1
  • 9
  • 23

2 Answers2

2

Use the -O switch to not have an owner defined in the dump.

Scott Marlowe
  • 8,490
  • 3
  • 23
  • 21
0

Not having an owner set is not normal Postgres design. To avoid having to login to your postgres database you can setup a .pgpass file. This is a plain text file and should be set with 600 permissions. The contents will look like:

hostname:port:database:username:password

Each connection can be on one line.

Additionally, for local db connections, assuming you have setup the rest of your security properly (ssh certs, etc) you can edit your pg_hba.conf file and set local connection authentication method to "trust." This is obviously not recommended for production or sensitive data. The line would look like this:

local   all             all                                     trust

The default method is "peer". Unless you set your username in a .pgpass file you will still need to connect with psql -U postgres but you will not need to enter a password.

Ilion
  • 6,772
  • 3
  • 24
  • 47