PostgreSQL notes

Quick commands

# ALTER DATABASE name RENAME TO newname

# mysql: SHOW DATABASES
# pgres: \l
# mysql: SHOW TABLES
# pgres: \d
# mysql: USE mydbname
# pgres: \c  mydbname # \connect

# mysql: SHOW COLUMNS
# pgres: \d table

# mysql: DESCRIBE TABLE
# pgres: \d+ table ( \dt)

# exit: \q

SELECT version();
show data_directory;

HOW TO

export/import

dump command

pg_dump -U USERNAME DBNAME > dbexport.pgsql
# PGPASSWORD="mypassword" pg_dump -U myusername dbname 密碼 > output.sql
pg_dump -U postgres -f /tmp/dump.sql.gz --compress=5 --no-owner dbname

import command

psql -f backup.sql dbname dbuser
# import specific table
pg_dump -U xxx public.TABLE_NAME DATABASE_NAME > out.sql
pg_dump -U xxx -d DB_NAME -t TABLE_NAME > out.sql

export csv

psql -U user -d db_name -c "Copy (Select * From foo_table LIMIT 10) To STDOUT With CSV HEADER DELIMITER ',';" > foo_data.csv

Slow query log

# 1. find config file
psql -U postgres -c 'SHOW config_file'

# 2. edit conf
# default
#log_min_duration_statement = -1
log_min_duration_statement = 1000 # (log all queries executing more than 1 second)

possible log paths:

/var/lib/pgsql/PG_VERSION/data/log/
/var/log/postgresql/
/var/lib/postgresql/PG_VERSION/main/pg_log

restart postgres (not “service”)

su - postgres -c "PGDATA=$PGDATA {path/to/}pg_ctl -w restart"

create user

postgres=# CREATE DATABASE mydbname ;
postgres=# CREATE DATABASE mydbname  OWNER myusername ;
postgres=# GRANT ALL PRIVILEGES ON DATABASE mydbname to myusername ;
ALTER ROLE myusername WITH superuser;

Problems

sequences

duplicate key error... (after restore data)

SELECT setval('my_sequence_name', (SELECT max(id) FROM my_table));
; my_sequence_name default is my_table_id_seq

truncate & auto increment

TRUNCATE table_name RESTART IDENTITY;