Dump and Restore DB in Postgres Ubuntu (this is a draft, hope will be better explained)

Open terminal and execute to use postgres user sudo -i -u postgres Note that the terminal now is -> postgres@user:~$
Create a database executing the command createdb mydatabasename
Create an user executing the command createuser user -P this is going to ask a password
Assign postgres role to the local db user executing the following command in psql console:
```
psql
grant postgres to user
```
Create .pgpass file to store the credentials of database to be "dumped". Execute:
```
touch .pgpass
nano .pgpass
```
and write the remote db information (to be backed up) following the format presented below.

hostname:port:database:username:password

Note that this file accepts multiple entries in the form:
```
hostname1:port:database1:username1:password1
hostname2:port:database2:username2:password2
```
Is necessary to give permissions to the created pgpass file, for this execute: chmod 0600 ~/.pgpass

https://stackoverflow.com/questions/2893954/how-to-pass-in-password-to-pg-dump https://www.postgresql.org/docs/current/static/libpq-pgpass.html
Execute the following command to make de dump from the remote database and restore to the local created database: https://dba.stackexchange.com/questions/55291/copy-postgresql-database-from-a-remote-server

pg_dump -h remote.db.host -U remote_user -C remote_db_name | psql -h localhost -d local_db_name -U local_db_user

This command will ask the password of the local created user, put it and hit enter.

To avoid permission errors when doing the restore use the flags listed below:

    pg_dump database_name -Fc --clean --create --no-owner --no-acl -h remote.db.host -U remote_db_user -p 5432 > my_dump.dump

- `-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

Another alternative is to create a file with the dump and make a restore

Create the dump of the remote database in file executing the following command:

pg_dump -Fc -h remote.db.host -U remote_user -C remote_db_name > my_db_file.dump

After that use the created file "my_db_file.dump" to restore in the already created database running at localhost executing the following command:
```
pg_restore -h localhost -p 5432 -d local_db_name -U local_db_user my_db_file.dump 
```
If errors appear in the process due to permissions on data that others users owns in the dump, put the -n public argument to the pg_restore command to only restore information from public schema. (https://stackoverflow.com/a/11776053/2860519)
```
pg_restore -h localhost -p 5432 -n public -d local_db_name -U local_db_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 local_db_name --no-owner --role= your_new_owner -U local_db_user my_db_file.dump 
```
To find the dump file location navigate to /var/lib/postgresql folder:

cd /var/lib/postgresql

Here you can copy the file to another location (for example Desktop) doing:

cp dump_file.dump ~user/Desktop/

Usefull commands in psql

after connect to psql console:

Command	Description
`\l`	see database list
`\du`	see user list

more commands here: http://www.postgresqltutorial.com/psql-commands/

juandm/Dump&RestoreDB.md

Dump and Restore DB in Postgres Ubuntu (this is a draft, hope will be better explained)