# -  - #
# - pg_dump - #
https://www.postgresql.org/docs/9.0/static/app-pgdump.html
http://www.postgresqltutorial.com/postgresql-backup-database/


# - run pg_dump without password - #
https://stackoverflow.com/questions/2893954/how-to-pass-in-password-to-pg-dump

PGPASSWORD="mypass" pg_dump mydb > mydb.dump
pg_dump --dbname=postgresql://username:password@127.0.0.1:5432/mydatabase
postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
export MYDB=postgresql://username:password@127.0.0.1:5432/mydatabase
PGPASSWORD="mypass" pg_dump -i -h localhost -p 5432 -U username -F c -b -v -f dumpfilename.dump databasename

0 3 * * * pg_dump --dbname=$MYDB | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz





# -  - #
# - ~\.pgpass - #
https://www.postgresql.org/docs/current/static/libpq-pgpass.html


The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

This file should contain lines of the following format:

hostname:port:database:username:password


https://blog.sleeplessbeastie.eu/2014/03/23/how-to-non-interactively-provide-password-for-the-postgresql-interactive-terminal/


server:port:database:username:password

localhost:5432:bookmarks:milosz:JOAvaDtW8SRZ2w7S
10.0.0.15:5432:wikidb:mediawiki:631j7ZtLvSF4fyIR
10.0.0.113:*:*:development:iGsxFMziuwLdEEqw

~\.pgpass
chmod 0600 ~/.pgpass

localhost:5432:IntegritySystems:is_derayo:Administrator2
localhost:5432:IntegritySystems:postgres:Admin22
localhost:5432:world:is_derayo:Administrator2
localhost:5432:world:postgres:Admin22



(You can add a reminder comment to the file by copying the line above and preceding it with #.) Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or \, escape this character with \. A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. The database field is of limited usefulness because users have the same password for all databases in the same cluster.

On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.











# Backup a single database
pg_dump -Fc db_name > db_backup.dmp
pg_dump -Fc mydb > db.dump

# Restore a single database
pg_restore -d db_name db_backup.dmp

# Can use pg_dumpall to backup all global information
# then use pg_dump to backup each database
pg_dumpall > global_only_backup.sql --globals-only



# Backup a database cluster
pg_dumpall | gzip > cluster_backup.gz

# Restore a database cluster
gunzip -c cluster_backup.gz | psql postgres⁠⁠⁠⁠




If your database cluster has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example:

CREATE DATABASE foo WITH TEMPLATE template0;


Examples

To dump a database called mydb into a SQL-script file:
$ pg_dump mydb > db.sql

To reload such a script into a (freshly created) database named newdb:
$ psql -d newdb -f db.sql

To dump a database into a custom-format archive file:
$ pg_dump -Fc mydb > db.dump

To reload an archive file into a (freshly created) database named newdb:
$ pg_restore -d newdb db.dump

To dump a single table named mytab:
$ pg_dump -t mytab mydb > db.sql

To dump all tables whose names start with emp in the detroit schema, except for the table named employee_log:
$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

To dump all schemas whose names start with east or west and end in gsm, excluding any schemas whose names contain the word test:
$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

The same, using regular expression notation to consolidate the switches:
$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

To dump all database objects except for tables whose names begin with ts_:
$ pg_dump -T 'ts_*' mydb > db.sql


To specify an upper-case or mixed-case name in -t and related switches, you need to double-quote the name; else it will be folded to lower case (see Patterns). But double quotes are special to the shell, so in turn they must be quoted. Thus, to dump a single table with a mixed-case name, you need something like

$ pg_dump -t '"MixedCaseName"' mydb > mytab.sql





# -  - #
# - pg_dump - Muy importante - #
https://www.commandprompt.com/blog/a_better_backup_with_postgresql_using_pg_dump/


The standard for portable backups with PostgreSQL is pg_dump and pg_dumpall. When used properly pg_dump will create a portable and highly customizable backup file that can be used to restore all or part of a single database. The pg_dump application acts as a standard PostgreSQL client. This means that you can perform this backup procedure from any remote host that has access to the database. You do not need to be a super user to use pg_dump but you must have read (and EXECUTE for functions) access to every object within the database. Backups created by pg_dump are internally consistent, meaning, the dump represents a snapshot of the database at the time pg_dump began running. The backup will not block other operations on the database while it is working. (Exceptions are those operations that need to operate with an exclusive lock, such as most forms of ALTER TABLE.) The minimum useful syntax for pg_dump is:

pg_dump dbname > outfile

However, the backup created from this method has limited usefulness. 
It can be used to restore a single database in full. 
A more useful and proper form of PostgreSQL backup syntax looks like this:



pg_dump -U $username --format=c --file=$mydatabase.sqlc $dbname

 -U, --username=NAME      connect as specified database user
 -F, --format=c|t|p       output file format (custom, tar, plain text)
 -f, --file=FILENAME      output file name


pg_dump -U postgres -W -F t dvdrental > \pgbackup\dvdrental.tar

-U postgres:  specifies the user to connect to PostgreSQL database server. We used postgres in this example.
-W:  forces pg_dump to prompt for the password before connecting to the PostgreSQL database server. 
     After you hit enter, pg_dump will prompt for the password of postgres user.
-F : specifies the output file format that can be one of the following:
     c: custom-format archive file format
     d: directory-format archive
     t:tar
     p: plain text SQL script file).





The most important of which is --format. 
By default pg_dump uses the plain text format. 
The plain text format is useful for very small databases with a minimal number of objects but other than that, it should be avoided. 
The custom format allows for a wealth of customizability. 
Using the custom format you are able to restore single objects from a backup. 
For example to restore only a specified index from a backup file:

pg_restore -U $username --dbname=$dbname --index=$indexname

If you wanted to restore only a single function:

pg_restore -U $username --dbname=$dbname --function=$functionname(args)

If you wanted to restore only a single table:

pg_restore -U $username --dbname=$dbname --table=$tablename

For more information on all the pg_dump options, please see the reference page. Restoring the dump The command used to restore a backup file is pg_restore. It has similar options to pg_dump. A simple restore:

pg_restore -U$username --dbname=$databasename $filename

Where filename is the name of the backup file.

    Do not confuse --file with $filename. The --file option is used to turn a custom format backup into a plain text backup. The value of --file will be used as the output file for that transformation.

If you make the mistake of creating a plain text backup, pg_restore can not be used as a restoration mechanism. You can use psql to restore it:

psql $dbname < $backupfile

Backing up every database The "postgresql" way of backing up every database is to use the command pg_dumpall. Unfortunately pg_dumpall can only create plain text backups and should be considered deprecated. However it is the only way to backup the globals in your cluster. A reasonable backup strategy to backup your globals and produce a flexible backup of every database in the cluster would look like this:

pg_dumpall -g -U$username --file=$globals.sql; 
psql -AtU postgres -c "SELECT datname FROM pg_database \
                          WHERE NOT datistemplate"| \
while read f; 
   do pg_dump -Upostgres --format=c --file=$f.sqlc $f;
done;

If someone knows of some Windows code that produces a similar result, it would be great if you would share.

    Remember, pg_dumpall creates a plain text backup. This means you will need to use psql to restore the globals backup file.

After restoring a backup, make sure you run ANALYZE to update the statistics. I know this isn't as comprehensive as it could be, but hey, its just a blog.