This is a collection of the most common commands I run while administering Postgres databases.
http://www.postgresql.org/download/linux/ubuntu/ https://help.ubuntu.com/community/PostgreSQL
sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" > \
/etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install -y postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5
sudo su - postgres
psql@see http://www.postgresql.org/docs/9.2/static/app-psql.html
psql --username=<username> --dbname=<database> --host=<hostname>
psql -U <username> -d <database> -h <hostname>\conninfo\q
\!\l\c <database_name>http://www.postgresql.org/docs/current/static/sql-createdatabase.html
CREATE DATABASE <database_name> WITH OWNER <username>;http://www.postgresql.org/docs/current/static/sql-dropdatabase.html
DROP DATABASE IF EXISTS <database_name>;SELECT rolname FROM pg_roles;http://www.postgresql.org/docs/current/static/sql-createuser.html
CREATE USER <user_name> WITH PASSWORD '<password>';http://www.postgresql.org/docs/current/static/sql-dropuser.html
DROP USER IF EXISTS <user_name>;http://www.postgresql.org/docs/current/static/sql-alterrole.html
ALTER ROLE <user_name> WITH PASSWORD '<password>';http://www.postgresql.org/docs/current/static/sql-grant.html
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;GRANT CONNECT ON DATABASE <db_name> TO <user_name>;GRANT USAGE ON SCHEMA public TO <user_name>;GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;\dn
SELECT schema_name FROM information_schema.schemata;
SELECT nspname FROM pg_catalog.pg_namespace;http://www.postgresql.org/docs/current/static/sql-createschema.html
CREATE SCHEMA IF NOT EXISTS <schema_name>;http://www.postgresql.org/docs/current/static/sql-dropschema.html
DROP SCHEMA IF EXISTS <schema_name> CASCADE;\dt
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;\dt *.*.
SELECT * FROM pg_catalog.pg_tables\d+ <table_name>
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';http://www.postgresql.org/docs/current/static/sql-createtable.html
CREATE TABLE <table_name>(
<column_name> <column_type>,
<column_name> <column_type>
);\d <table_name>http://www.postgresql.org/docs/current/static/sql-droptable.html
DROP TABLE IF EXISTS <table_name> CASCADE;http://www.postgresql.org/docs/current/static/sql-altertable.html
ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;http://www.postgresql.org/docs/current/static/sql-insert.html
INSERT INTO <table_name> VALUES( <value_1>, <value_2> );http://www.postgresql.org/docs/current/static/sql-select.html
SELECT * FROM <table_name>;SELECT * FROM <table_name> LIMIT 1;SELECT * FROM <table_name> WHERE <column_name> = <value>;http://www.postgresql.org/docs/current/static/sql-update.html
UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;http://www.postgresql.org/docs/current/static/sql-delete.html
DELETE FROM <table_name>
WHERE <column_name> = <value>;psql -U <username> -d <database> -h <host> -f <local_file>http://www.postgresql.org/docs/current/static/app-pgdump.html
pg_dump -a <database_name>
pg_dump --data-only <database_name>pg_dump -s <database_name>
pg_dump --schema-only <database_name>pg_restore -d somedb dump.dmphttp://www.postgresql.org/docs/current/static/sql-copy.html
COPY <table_name> FROM '<file_path>' DELIMITER ',' CSV;
\copy <table_name> FROM '<file_path>' DELIMITER ',' CSVCOPY <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV;
\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV