Postgres Reminder
Finally moving this out of notes.txt into the blog for future reference.
To kill a connection:
SELECT pid FROM pg_stat_activity where pid <> pg_backend_pid();
SELECT pg_terminate_backend($1);
To dump a database into a SQL-script file:
pg_dump db_name > outfile.sql
To dump a database into a compressed file:
pg_dump -Fc db_name > outfile.dump
To restore a database:
psql db_name < infile.sql
To create a user:
CREATE USER johndoe WITH ENCRYPTED PASSWORD 'some_password';
To grant all privileges to a user:
GRANT ALL PRIVILEGES ON DATABASE db_name TO db_user;
log in as admin :
psql -U postgres
Change a user’s password:
ALTER USER "user_name" WITH PASSWORD 'new_password';
To view all users:
\du
To connect to a database as a specific user:
psql -d database_name -U user
To change a database’s owner:
ALTER DATABASE name OWNER TO new_owner;