Skip to content

Instantly share code, notes, and snippets.

@chrisvoo
Last active October 6, 2016 04:13
Show Gist options
  • Save chrisvoo/ddb1a7df5d9a5646dc73 to your computer and use it in GitHub Desktop.
Save chrisvoo/ddb1a7df5d9a5646dc73 to your computer and use it in GitHub Desktop.
psql/pg_dump administration commands
# dump of your database in plain text. It creates tables, views, etc
# for exporting multiple tables only, you can repeat parameter -t
pg_dump --host <IP> --port 5432 --username "myuser" \
--format plain --create --encoding UTF8 -v \
--file "/my/path/file.sql" "db_name"
# or, with short opts and wrapping all SQL statements inside a transaction with automatic ROLLBACK
psql -U postgres -p 5433 --single-transaction -f /my/path/file.sql db_name
# Import of a SQL dump. Be sure to have write permissions in the current directory
psql -U username -d database -h server_destination -f mydump.sql 2>import_error.log
# ~/.psqlrc file for automatic settings when connecting to PG from psql client
\x auto # vertical expansion of records
\pset null ¤ # symbol used for null values
# RESET PostgreSQL and OS cache, useful to track times and avoid cache results
# See https://linux-mm.org/Drop_Caches
ccastelli@daneel:~$ sudo service postgresql stop
ccastelli@daneel:~$ sudo su
root@daneel:~ echo 3 > /proc/sys/vm/drop_caches && service postgresql start
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment