psql/pg_dump administration commands

pgsql_administration.sh

# 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