Properly create and restore your development postgreSQL on production

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 deploy@192.168.111.222:/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:

http://stackoverflow.com/questions/12924466/capistrano-with-postgresql-error-database-is-being-accessed-by-other-users

or

http://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server?rq=1

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.