search by tags

for the user

adventures into the land of the command line

postgres cheat sheet

installing postgres and starting it

$ yum install postgresql-server.x86_64
$ mkdir -p /mnt/data/postgresql
$ chown postgres:postgres /mnt/data/postgresql
$ mkdir /var/log/postgres
$ chown postgres:postgres /var/log/postgres
$ su - postgres
-bash-4.1$ initdb -D /mnt/data/postgresql
-bash-4.1$ vim /mnt/data/postgresql/postgresql.conf

#on line 246: log_directory = 'pg_log', change to:
log_directory = '/var/log/postgres'

-bash-4.1$ pg_ctl -D /mnt/data/postgresql -l logfile start

edit the startup script so restart doesnt fail when it cant find the default data directory

$ vim /etc/init.d/postgresql

#on lines 45-48:

#PGDATA=/var/lib/pgsql/data
#PGLOG=/var/lib/pgsql/pgstartup.log
PGDATA=/mnt/data/postgresql
PGLOG=/var/log/postgres/pgstartup.log


$ chkconfig postgresql on

logging in

$ psql -U postgres
$ psql -d somedb -U postgres

help commands

postgres=# \h
postgres=# \?

databases and schemas

create a database

postgres=# CREATE DATABASE somedb;

show all databases

postgres=# \l

create a schema

postgres=# CREATE SCHEMA someschema;

show all schemas

postgres=# \dn[+]

remove a database

postgres=# DROP DATABASE somedb;

remove a schema

postgres=# DROP SCHEMA someschema;

users (roles) & permissions

create a user that exists on the localhost with a password, that automatically has login privileges

postgres=# CREATE USER someuser WITH PASSWORD 'somepassword';
postgres=# CREATE ROLE someuser WITH LOGIN;

create a user that doesn’t have login privileges

postgres=# CREATE ROLE someuser;

show roles (users)

postgres=# \du[+]

show permissions for roles (users)

postgres=# \du

           List of roles
 Role name | Attributes  | Member of
-----------+-------------+-----------
 someuser  |             | {}
 postgres  | Superuser   | {}
           : Create role   
           : Create DB
           : Replication

edit grant attributes for a role (user)

postgres=# ALTER ROLE someuser WITH NOLOGIN;
postgres=# ALTER USER someuser WITH PASSWORD 'somepassword';
postgres=# ALTER USER someuser WITH ENCRYPTED PASSWORD 'somepassword';

grant permissions for a role (user) on a table or schema.table

postgres=# GRANT USAGE ON SCHEMA someschema to someuser;
postgres=# GRANT SELECT, UPDATE, INSERT, DELETE ON someschema.sometable to someuser;

grant permissions for a role (user) on a database

postgres=# GRANT CREATE, CONNECT ON DATABASE somedb to someuser;
postgres=# GRANT ALL PRIVILEGES ON DATABASE somedb to someuser;

some (but not all) privilege keywords that can be granted

ALL PRIVILEGES

ALTER               
CREATE              
DROP
SET
DELETE

DATABASE
ROLE
TABLE
USER

revoke permissions for a user

postgres=# REVOKE INSERT ON somedb.sometable FROM someuser;

remove a user

postgres=# DROP USER someuser;
postgres=# DROP ROLE someuser;

tables

show tables

postgres=# \dt[S+]

create a table, the syntax is column name, data type.

CREATE TABLE someschema.sometable (
uuid uuid UNIQUE,
email varchar(40),
password varchar(60),
date_registered timestamp,
last_login timestamp,
last_logout timestamp,
currently_logged_in bool,
nickname varchar(32),
currency varchar(3),
period_recurrency varchar(9),
origin_date date,
origin_funds numeric(8,2),
email_confirmed bool,
subscribed bool,
token varchar(173),
PRIMARY KEY(uuid)
);

show columns from a table in a database

postgres=# \d[+] sometable;

show all data from a table in a database

postgres=# SELECT * FROM sometable;

show data from a specific column in a table in a database

postgres=# SELECT email FROM someschema.sometable;
postgres=# SELECT DISTINCT remote_ip FROM someschema.sometable;
postgres=# SELECT SUM(bytes_sent) FROM someschema.sometable;
postgres=# SELECT logged_time FROM sometable ORDER BY id DESC LIMIT 1
postgres=# SELECT logged_time, remote_ip FROM somedb.sometable;
postgres=# SELECT COUNT(*) FROM sometable WHERE request_uri LIKE '%PUT%' OR '%COPY%' OR '%POST%' OR '%LIST%' AND logged_time >= '[01/Feb]' AND logged_time

remove all data from a table

postgres=# TRUNCATE someschema.sometable;

edit the data in columns in a table

postgres=# INSERT INTO sometable.users(email,date_registered) VALUES([email protected],TIMESTAMP WITH TIME ZONE '2015-05-17 18:57:29+10')

postgres=# UPDATE sometable.users SET last_login=TIMESTAMP WITH TIME ZONE '2015-05-17 18:57:29+10', currently_logged_in=True WHERE uuid = '54103246-c092-4798-986b-4407e52764c8';

edit the actual columns

postgres=# ALTER TABLE someschema.sometable RENAME COLUMN recurrance TO recurrence;

postgres=# ALTER TABLE someschema.sometable ADD COLUMN origin_date date;

remove a table

postgres=# DROP TABLE sometable;

import and export

export a databases

$ pg_dump somedb > somedb.sql

import a database dump

$ psql somedb < somedb.sql

config files & logs

config file locations

by default, postgres is installed into /var/lib/pgsql/data unless you specify the initdb with -D

$ initdb -D /path/to/somewhere/else
$POSTGRESHOME/postgresql.conf # main config file
$POSTGRESHOME/pg_hba.conf # host based authentication config file
$POSTGRESHOME/pg_ident.conf # user name mapping config file

log files

by default, postgres logs go into /var/lib/pgsql/ unless you specify something different on the command line or in the init.d script or in the config file

pgstartup.log
postgresql-Day.log