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;
Top comments (0)