PostgreSQL - Tips


psql

  1. https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS

login

$ psql -h localhost -U USERNAME DATABASE
/ enter password for database user USERNAME

as postgres user:

$ sudo -u postgres psql -U postgres DATABASE

users

http://www.postgresqltutorial.com/postgresql-roles

A role can be a user or a group, depending on how you setup the role. A role that has login right is called user. A role may be a member of other roles, which are known as groups.

databases

tables

extensions

on macOS extensions are located in /usr/local/Cellar/postgresql/9.6.3/share/postgresql/extension/.

query optimization

  1. https://robots.thoughtbot.com/why-postgres-wont-always-use-an-index

Rails notes

LIKE operator

  1. https://stackoverflow.com/questions/1566717

backup

using pg_dump

dump

there are 2 ways to create a dump file (say, dump.sql):

$ pg_dump -h localhost -U USERNAME -p PORT -f dump.sql DATABASE
$ tar cvzf dump.sql.tar.gz dump.sql

NOTE: use postgres user on macOS.

use -aO options to dump the data only:

$ pg_dump -h localhost -U USERNAME -p PORT -aOf dump.sql DATABASE
$ tar cvzf dump.sql.tar.gz dump.sql

restore

$ tar xvzf dump.sql.tar.gz
$ psql -h localhost -U USERNAME -p PORT -f dump.sql DATABASE

it might be necessary to download the dump file from remote host first:

$ scp SSH_HOST:~/tmp/dump.sql.tar.gz dump.sql.tar.gz

[Rails] using backup gem (structure + data)

create:

$ RAILS_ENV=production bundle exec backup perform -t model_name -c ./config.rb

restore:

/ download backup archive
$ tar xvf model_name.tar
$ cd model_name/databases/
$ gunzip PostgreSQL.sql.gz
$ psql -h localhost -U USERNAME -f ./PostgreSQL.sql DATABASE
$ RAILS_ENV=test rails db:structure:load

say, to restore our database running in Docker:

$ psql -h localhost -p 5434 -U postgres -f ./PostgreSQL.sql sith_dev

(how to) remove all versions of PostgreSQL on Ubuntu

  1. https://askubuntu.com/a/32735
$ sudo apt-get --purge remove postgresql postgresql-doc postgresql-common

(how to) move local database into Docker container

  1. https://github.com/wsargent/docker-cheat-sheet

add db service

docker-compose.yml:

  services:
+   db:
+     image: postgres:11.1
+     environment:
+       POSTGRES_USER: postgres
+       POSTGRES_PASSWORD: postgres
+     ports:
+       - 5434:5432

create databases in Docker container

Rails:

$ rails db:create
$ RAILS_ENV=test rails db:create

Phoenix:

$ mix ecto.create
$ MIX_ENV=test mix ecto.create

import local database into Docker container

$ DB_USER=postgres
$ DB_NAME=reika_dev
$ DOCKER_PSQL_CMD='docker-compose exec -T db psql -U "${DB_USER}" -d "${DB_NAME}" -v ON_ERROR_STOP=1'

NOTE: remove -v ON_ERROR_STOP=1 to skip errors (say, you’ll get errors if structure has been already loaded).

run psql

$ psql -h localhost -p 5434 -U postgres -d reika_dev

(how to) get timezone offset

SELECT * FROM pg_timezone_names WHERE name='Europe/Moscow';

(how to) drop database

the point is that you can’t connect to the database you’re going to drop - you’ll get the error then:

ERROR:  cannot drop the currently open database

connect to postgres database as superuser instead:

$ psql -U devops -d postgres -h localhost
postgres=# DROP DATABASE IF EXISTS eva_prod;
DROP DATABASE

(how to) remove PostgreSQL completely

$ sudo apt autoremove --purge postgresql-12
$ sudo rm -rf /etc/postgresql
$ sudo rm -rf /run/postgresql
$ sudo rm -rf /var/lib/postgresql
$ sudo apt install postgresql-12