Dump and Restore postgres Database

1. Dump

In this gist we are going to use the pg_dump tool to back up a remote postgres database and pg_restore to restore the dump in a local database.

If postgres is not installed directly in your machine because you are usign it via docker containers, first you should install the postgresql client utility in your host machine, this client includes psql and pg_dump tools.
```
$ sudo apt install postgresql-client  
```
To avoid put the password every time we execute the command we will going to use a postgres password file (.pgpass), create it in the home folder executing the following command.
```
$ cd ~
$ touch .pgpass
```
In the .pgpass file write the remote db information (to be backed up) following the format presented below.
```
server:port:database:username:password
```
Note that this file accepts multiple entries in the form:
```
hostname1:port:database1:username1:password1
hostname2:port:database2:username2:password2
```
As stated in the postgres docs you should give the rigth permission to the file executing the command below.

"On Unix systems, the permissions on a password file must disallow any access to world or group; achieve this by a command such as chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored."
```
$ chmod 0600 ~/.pgpass
```
To make the dump execute the following command replacing the database name, user and host, note that the command should not ask for password If it ask see Troubleshooting section.
```
$ pg_dump database_name -Fc --clean --create --no-owner --no-acl -h remote.db.host -U remote_db_user -p 5432 > my_db_file.dump
```
Command Explication
- -Fc: custom format (to save as .dump)
- --clean: Output commands to clean (drop) database objects prior to outputting the commands for creating them.
- --create: Begin the output with a command to create the database itself and reconnect to the created database.
- --no-owner: Do not output commands to set ownership of objects to match the original database.
- --no-acl: Prevent dumping of access privileges (grant/revoke commands).
- -h: Database host
- -U: Database User
- -p: Database port

After command completion the created file "my_db_file.dump" is already to be restored in other database.

Restore

To restore the database we will use the pg_restore tool.

First of all lets create the database and a role in the localhost:

Create the local database
- Using a local insatallation of postgresql
  1. Open terminal and execute to use postgres user:
```
$ sudo -i -u postgres 
```
  Note that the terminal now is -> postgres@user:~$
  1. Create a database executing the command
```
$ createdb my_database_name
```
  2. Create an user executing the command. This is going to ask a password.
```
$ createuser user -P
```
  3. Assign postgres role to the local db user executing the following command in psql console:
```
$ psql
$ grant postgres to user
```
- Using a postgres docker container
  1. Run the postgres container
```
docker run --rm --name pg-docker -d -p 5432:5432 -v $HOME/dev/docker/volumes/postgres/:/var/lib/postgresql/data postgres    
```
    Options:
    - --rm: used to remove the container when it stops
    - --name: give a name to the container.
    - -d: Run in detached mode, it means in background.
    - -p: Map the host port 5432 to the container's port 5432.
    - -v: Setup a volume between host and container file system.
  2. Create a database executing the command:
```
$ docker exec pg-docker psql -U postgres -c "create database my_database_name"
```
  3. Create an user executing the command:
```
$ docker exec pg-docker psql -U postgres -c "create user my_user superuser password 'my_password'" 
```
  4. Grant permissions on the database to the created user:
```
$ docker exec pg-docker psql -U postgres -c "grant all privileges on database my_database_name to my_user"
```
Execute the database restore

In the following example we are going to restore the dump in a local postgres instance.
```
$ pg_restore -h localhost -p 5432 -d my_database_name -U my_user my_db_file.dump 
```
If errors appear in the process due to permissions on data that others users owns in the dump, and the data that is being restored is all in the public schema, put the -n public argument to the pg_restore command to only restore information from this schema. (https://stackoverflow.com/a/11776053/2860519)
```
$ pg_restore -h localhost -p 5432 -n public -d my_database_name -U my_user my_db_file.dump 
```
Also, there is a chance to get errors due to ownership because the dump has other user, to avoid this problems in the restore use the flags --no-owner --role= your_new_owner
```
$ pg_restore -h localhost -p 5432 -n public -d my_database_name --no-owner --role= my_user -U my_user my_db_file.dump     
```
For docker use the following commands:
- First of all, copy the dump to the conainer volume:
```
$ docker cp my_dump.dump pg-docker:/var/lib/postgresql/data 
```
- Once copied execute the following command to restore the database.
To delete the database before restore use:
```
docker exec -it <container-id> psql -U <username> -d postgres -c "DROP DATABASE <dbname>;"
```
To create the database use:
```
docker exec -it <container-id> psql -U <username> -d postgres -c "CREATE DATABASE <dbname>;"
```
```
$ docker exec pg-docker pg_restore -h localhost -p 5432 -d my_database_name -U my_user --no-owner --role=my_role /var/lib/postgresql/data/my_dump_file.dump
```

Troubleshooting

If the pg_dump command is still asking the password after create the pgpass file, try the following steps:

Verify the file permisions as stated in point 4 of Dump section.
Set the file owner as the same user which is logged in:
```
$ sudo chown login_username:login_username ~/.pgpass
```
Verify PGPASSFILE environment variable.

To see it execute:
```
$ echo $PGPASSFILE
```
To set it execute the following command, replacing the correct user:
```
$ export PGPASSFILE='/home/user/.pgpass'
```
If you want to unset the variable execute:
```
$ unset PGPASSFILE
```

juandm/DumpPostgresDB.md

Dump and Restore postgres Database

1. Dump

Restore

Troubleshooting

References