So maybe you are like me, who is parsing a lot of static data into a development database, make an app around this data and then you don’t want to have an empty database on production, but instead you want to have a full copy of your development database.
After spending a few hours trying to make it work, there is one solution, that yielded exactly 0 errors (yay!)
On your local machine (in the shell, wherever you currently are) :
pg_dump --no-owner your_db_develoment > backup.bak
Then put it in some safe place on your remote machine:
scp backup.bak email@example.com:/home/restore/backup.bak
Make a backup of your production database for safety sake (on your production server, of course)
pg_dump --no-owner your_db_production > /home/backups/04-03-2016/backup.bak
If there is no production database yet on the server, proceed with creating it, elsewise you might need (if the app has users) to cut the current sessions, so check these out:
Ok, now we have no database on production machine at all. Let’s create new production database:
postgres=# CREATE DATABASE database_production; CREATE DATABASE postgres=# CREATE USER production_db_user WITH password 'qwerty'; CREATE ROLE postgres=# GRANT ALL privileges ON DATABASE database_production TO production_db_user; GRANT
Make sure to populate the password with the same password, as set up in database.yml for production user.
That’s it! No need to change the owner. Now let’s restore the backup file:
sudo psql -d mydb_production -U production_user < /home/deploy/restore/backup.bak; Password for user production_user: SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql SET SET SET CREATE TABLE CREATE SEQUENCE ALTER SEQUENCE CREATE TABLE CREATE SEQUENCE ALTER SEQUENCE CREATE TABLE CREATE SEQUENCE ALTER SEQUENCE CREATE TABLE ALTER TABLE ALTER TABLE ALTER TABLE COPY 14964 setval -------- 114041 (1 row) COPY 3151 setval -------- 3236 (1 row) COPY 28011 setval -------- 7081 (1 row) COPY 8 ALTER TABLE ALTER TABLE ALTER TABLE CREATE INDEX CREATE INDEX CREATE INDEX ALTER TABLE ALTER TABLE
As of now I have no issues regarding the one error above.