PostgreSQL
## start $ psql
## open database
$ psql --host=${host} --dbname=${database} --username=${user} --password
## exit ~$ postgres=# \q
Installation
$ sudo apt-get install postgresql postgresql-contrib
Databases
## create
~$ postgres=# CREATE DATABASE ${database};
## list ~$ postgres=# \l
## URL
## DATABASE_URL=postgresql://${user}:${password}@postgres:5432/drkiq?encoding=utf8&pool=5&timeout=5000
## select database
~$ postgres=# \c ${database};
## backup
PGPASSWORD="${password}" pg_dump -c -d ${database} -U ${user} -h localhost > dump_`date +%Y-%m-%d`.sql
## restore
$ psql -d ${database} -f dump_`date +%Y-%m-%d`.sql
$ psql -f db.out postgres
## save table to file ~$ postgres=# COPY checkins TO '/tmp/checkins_db.csv' DELIMITER ',' CSV HEADER;
Users
## create
~$ postgres=# CREATE USER ${user} WITH PASSWORD '${password}';
## grant privileges
~$ postgres=# GRANT ALL PRIVILEGES ON DATABASE ${database} to ${user};
## show users ~$ postgres=# \du
## rename user
~$ postgres=# ALTER USER ${user} RENAME TO ${new_user};
## change password
~$ postgres=# ALTER USER ${user} WITH PASSWORD '${new_pswd}';
## test
$ psql --username=${user} --password
## forgot password
$ sudo nano /etc/postgresql/13/main/pg_hba.conf
< /etc/postgresql/13/main/pg_hba.conf >
local all all trust
host all postgres 127.0.0.1/32 trust
host all all ::1/128 trust
$ sudo /etc/init.d/postgresql restart
$ psql -h localhost -U postgres
~$ postgres=# ALTER USER postgres with password 'new_password';
## user authentication
$ sudo nano /etc/postgresql/13/main/pg_hba.conf
local all postgres md5
local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
Setup
## change listen addresses
$ sudo nano /etc/postgresql/13/main/postgresql.conf
< etc/postgresql/13/main/postgresql.conf >
listen_addresses = '*'
DOCKER AND PSQL:
Starting an instance:
# docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres
$ docker run --name image_db_1 -e POSTGRES_PASSWORD=pg1234 -d --volumes-from postgres-data postgres
# volumes-from: mount /var/lib/postgresql/data from postgres-data volume
# docker run --name local-pg -p 5432:5432 -d postgres
# docker run --name sw_db_backup -p 5432:5432 -d postgres:13
Connect to it from an application:
#$ docker run --name some-app --link some-postgres:postgres -d application-that-uses-postgres
Connect to it via psql:
#$ docker run -it --rm --link some-postgres:postgres postgres psql -h postgres -U postgres
! working !
$ docker run -it --link image_db_1:postgres --net the_app --rm postgres:13 sh -c 'exec psql -h postgres -p 5432 -U postgres'
ERROR:
# missing templates $ create database template0 TEMPLATE postgres; $ update pg_database set datistemplate=true where datname='template0';
Cannot link to image_db_1, as it does not belong to the default network
$ docker network ls
NETWORK ID NAME DRIVER SCOPE
39da4dfdd2f1 bridge bridge local
1457d75d42a7 host host local
b30a51375e67 none null local
d7ca18692329 the_app bridge local
0836c4154d76 app_test bridge local
$ docker run -it --link image_db_1:postgres --net the_app --rm postgres:13
Open a shell prompt:
! working !
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS
3c1add52fdad4 postgres:13 "docker-entrypoint.sh" 48 seconds ago Up
PORTS NAMES
0.0.0.0:5432->5432/tcp image_db_1
$ docker exec -it 3c1add52fdad4 sh
$ su - postgres
$ psql
$ \l
$ \c prod_db
$ \dt
Deleting volumes:
$ docker run --rm -v /var/lib/postgresql/data postgres:13
=================================
Data in db_1 is updated in real time.
Data in pg_data_1 is updated after committing.
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS
2d17d374f665 sw_db_image:v1 "docker-entrypoint.sh" 48 seconds ago Up
PORTS NAMES
5432/tcp image_db_1
# make sure POSTGRES_PORT_5432_TCP_ADDR = postgres and POSTGRES_PORT_5432_TCP_PORT = 5432
$ docker run -it --link image_db_1:postgres --rm postgres:13 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d prod_db'
$ docker run -it --link image_db_1:postgres --rm postgres:13 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d prod_db'
# list databases
$ \l
$ \c prod_db
$ \dt
$ select * from users;
$ docker commit -m "message" 2d17d374f665 sw_db_image:v2
Restart table index with 1:
$ psql
# get the sequence name
$ SELECT pg_get_serial_sequence('users','id');
pg_get_serial_sequence
-----------------------
public.users_id_seq
$ ALTER SEQUENCE 'users_id_seq' RESTART WITH 1;
BACKUPS:
# $ docker exec -t image_db_1 pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql $ docker exec -t image_db_1 pg_dumpall -c -U postgres > ../_backups/dump_`date +%Y-%m-%d`.sql # restore $ docker start sw_db_backup $ cat dump_06-02-2017_15_55_49.sql | docker exec -i sw_db_backup psql -U postgres $ docker run -it --link sw_db_backup:postgres --rm postgres:13 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d prod_db'
BACKUPS FROM HEROKU:
DATABASE_URL=postgres://arpgybtfkjuqur:osiTmzUq8tlscexAz6M_eaIb3N@ec2-54-243-200-159.compute-1.amazonaws.com:5432/dcjgjfffk4vdc0 # download the sql dump $ pg_dump --host=ec2-54-243-200-159.compute-1.amazonaws.com --port=5432 --username=arpgybtfkjuqur --password --dbname=dcjgjfffk4vdc0 > output.sql password: osiTmzUq8tlscexAz6M_eaIb3N
RESTORE FROM HEROKU:
$ cat output.sql | docker exec -i image_db_1 psql -U postgres -d prod_db $ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS 9e4f0fc31113 postgres:13 "docker-entrypoint.sh" 48 seconds ago Up PORTS NAMES 0.0.0.0:5432->5432/tcp image_db_1 $ docker exec -it 9e4f0fc31113 sh $ su - postgres $ psql $ \dt
OTHER COMMANDS:
# adding password $ CREATE USER user WITH PASSWORD 'user_password';
# adding privileges $ GRANT ALL PRIVILEGES ON DATABASE db to user;
# revoking privileges $ REVOKE ALL PRIVILEGES ON DATABASE db FROM user;
# deleting user $ DROP USER user;
# delete row $ DELETE FROM table1 WHERE name = 'something';
# create database $ CREATE DATABASE db_name
# delete database
# change a value
$ UPDATE pg_database SET datistemplate='false' WHERE datname='template0';
UPDATE 1
$ DROP DATABASE template0;
DROP DATABASE
# superuser privileges
# common errors:
# - must be owner of extension plpgsql
# - must be owner of schema public
$ ALTER ROLE user SUPERUSER;
# change table owenership $ ALTER TABLE table OWNER TO user;
# change password $ \password user
# sort database psql > select * from my_table where user = 'Username1' order by ID desc limit 2. # update a column for all rows in table psql > update "table_name" set col_name = 'val';
Updating PostgresSQL version:
$ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - $ sudo apt-get update $ sudo apt-get install postgresql-13
Changing postgresql port:
$ sudo nano /etc/postgresql/13/main/postgresql.conf
Changing pg_dump version:
$ find / -name pg_dump -type f 2>/dev/null
/usr/lib/postgresql/13/bin/pg_dump
Backup:
# $ PGPASSWORD="db_password" /usr/lib/postgresql/13/bin/pg_dump --host=xxxxx.us-east-1.rds.amazonaws.com --port=5432 --username=user1 --dbname=db1 > /home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql ssh xxx@xxxxxxx "PGPASSWORD="db_password" /usr/lib/postgresql/13/bin/pg_dump \ -h xxxxx.us-east-1.rds.amazonaws.com -p 5432 -U user1 \ -F c -b -v -f "/home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql" db1"
Restore
# psql -d db_data1 -f db_dump_`date +%Y_%m_%d`.sql # pg_restore -d db1 -U user1 -C /home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql PGPASSWORD="db_password" pg_restore --clean --host=localhost --dbname=db1 --username=user1 /home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql
count:
psql: select count(*) from table;
query:
SELECT column_name, count(*) from public.relation_name GROUP BY column_name ORDER BY column_name;
example:
SELECT program, COUNT(*) FROM public.all_hotels GROUP BY program ORDER BY program;
example:
SELECT * from table_name where column_name = 'some_value';
columns:
psql: \d+ table_name;
## update value at ${column} in ${table}
$ update ${table} set ${column}=md5('${new_value}') where ${column}='${some_value}';
options:
# expanded display \x on
misc:
# clear table (and reset index) truncate table "table_name" restart identity cascade;