Installing
- sudo apt update
- sudo apt install postgresql postgresql-contrib
- sudo service postgresql start
- sudo passwd postgres # then close and re-open the terminal(?)
- sudo -u postgres psql
- create user foo;
- alter user foo with superuser;
- alter user foo with password 'new_password';
- psql -Ufoo -d postgres
Reconfigure authentication if necessary to either require or disable passwords.
(source)
Using
connect: psql -U [username, e.g. postgres] -d [database]
- \c dbname = connect to dbname
- \l = list databases:
- \dt = describe tables, views and sequences
- \dt+ = describe tables with comments and sizes
- \dT = describe Types
- \di = describe indexes
- \q = quit
- \x = toggle equivalent of adding MySQL's \G at the end of queries to display columns as rows
- \connect database = change to a different database
- CREATE DATABASE yourdbname;
- CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
- GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
- Use auto_increment in a column definition:
- create sequence foo__id__seq increment by 1 no maxvalue no minvalue start with 1 cache 1;
- create table foo ( id integer primary key default nextval('foo__id__seq') );
- Reset an auto_increment counter (sequence):
- SELECT setval('sequence_name', 1, false); -- this works
- ALTER SEQUENCE sequence_name RESTART WITH 1; -- this also works
- See what's in an ENUM
- SELECT enumlabel FROM pg_enum WHERE enumtypid = 'myenum'::regtype ORDER BY id;
- Closest equivalent of MySQL's "show create table"
- pg_dump -U postgres --schema-only [database] >> dump.sql
- See current activity
- SELECT * FROM pg_stat_activity (equivalent to MySQL's "show processlist")
- Drop all current connections
- SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='foo'; # foo = database name
- pg_dump -Upostgres -hHOSTNAME DBNAME -fOUTPUTFILE.sql --no-password
- psql -Upostgres -hHOSTNAME -dDBNAME -fINPUTFILE.sql
- psql -Upostgres -hHOSTNAME -dDBNAME -c "Some SQL command"
Posts
