Coding Knowledge Center
PostgreSQL
DDL/DML |
CREATE ROLE <user> WITH createdb login; - Create user that can create databases and login create a role w/ creatdb and login perms
CREATE DATABASE <database>; - Create database
CREATE DATABASE <new_database> WITH TEMPLATE <old_database>; - Backup database
CREATE TABLE <table> as SELECT * FROM <table>; - Copy a table to backup
ALTER DATABASE <old_database> RENAME TO <new_database>; - Rename database
INSERT INTO <table> (SELECT * FROM <table>); - Copy record from one table to another
DELETE FROM <table> WHERE <column> > NOW() - INTERVAL '2 days'; - Delete records using intervals
ALTER USER <user> WITH PASSWORD 'VeryVerySecret'; - Change user password
COPY (SELECT * FROM <table> where seq=0 AND <column> NOTNULL) TO '/homes/staff/steve/cust_dump.csv' format csv; - Dump select data to a cvs formatted file
DDL/DML commands.
Dump |
pg_dump --inserts -t extauth portal > portal_extauth.sql - Dump database table
pg_dump --column_inserts --data-only -t <table> <database> > <database>_<table>.sql - Dump database table data only
pg_dump -U pgsql -F c <database> > <database>.dump - Dump database
pg_dump -U pgsql <database> > <dump_file> - Dump database
Dump databases.
Files |
sudo /usr/local/etc/rc.d/postgresql start/stop/restart - Start/Stop/Restart PostgreSQL
sudo vi /pg92/data/postgresql.conf - PostgreSQL main config
sudo vi /pg92/data/pg_hba.conf - PostgreSQL host config
~/.psqlrc - homedir config file
PSQL config files.
Grant |
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <user>; - Grant select privileges on all tables in schema to user set postgresql perms
GRANT SELECT ON ALL SEQUENCES IN SCHEMA <schema> TO <user>; - Grant select privileges on all sequences in schema to user
GRANT <user> TO <database>; - Grant access privileges to database for user
GRANT SELECT, UPDATE, INSERT, DELETE ON <table> TO <user 1>, <user 2>; - Grant privileges on table to users
GRANT ALL ON DATABASE <database> TO <user>; - Grant all privileges on database to user
GRANT USAGE ON SCHEMA <schema> to <user>; - Grant usage privileges on schema to user.
Grant various privileges.
psql |
\c [database] - Connect to database
\l - List pgsql databases
\d - List tables in pgsql databases
\dL - List langauges
\df - Display database functions
\sf fn_emp_id (no ; at end) - Display function definition
\sf admin.fn_reshistbyname(varchar(32),varchar(32)) - Display function definition
\df admin.fn_getbyextresid - Display function in schema
\du - Display list of roles
\dn - Display list of schemas
\dn+ - Display list of schemas & perms
\dp+ - Display list of tables & perms
\z - display list of tables/sequences & perms
PSQL commands.
Restore |
psql <databdase> < <dump_file> - Restore database
pg_restore -U pgsql -d <database> <database>.dump - Restore database
pg_dump -h <host 1> <database> | psql -h <host 2> <database> - Dump database locally & restore database remotely
Restore databases.
Show |
show port; - Show port being used by server
show listen_addresses; - Show listening addresses used by server
show search_path; - Show search path
Show server values.