pg_dump pg_restore postgresql backup postgres

pg_dump_pg_restore.sh

# Create and restore backups on PostgreSQL (tested on 9.2 and 9.3)

# create database backup
PGPASSWORD="XXX" pg_dump --no-owner --no-acl -U USER DATABASE -h HOST -F t > output_file.tar

# create database backup (heroku)
heroku pgbackups:capture
heroku pgbackups # show stored backups (assuming a117 is the desired one)
heroku pgbackups:url a117 # => it retrieves dump url on AWS S3

# upload dump to server
scp ~/Downloads/filename.dump user@remoteserver:~/destination_path

# restore backup
pg_restore -d DATABASENAME /tmp/path_to_filename.dump

pg_install

brew install postgres
initdb /usr/local/var/postgres
createuser -s postgres

psql -U postgres