DEV Community

moogoo
moogoo

Posted on

1

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

possible log paths:

/var/lib/pgsql/PG_VERSION/data/log/
/var/log/postgresql/
/var/lib/postgresql/PG_VERSION/main/pg_log
Enter fullscreen mode Exit fullscreen mode

restart postgres (not “service”)

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

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

truncate & auto increment

TRUNCATE table_name RESTART IDENTITY;
Enter fullscreen mode Exit fullscreen mode

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay