Postgres

Develop

Config file:

sudo vim /etc/postgresql/9.5/main/pg_hba.conf

check version

ActiveRecord::Base.connection.select_value('SELECT version()')

dump

-O, --no-owner               skip restoration of object ownership in plain-text format
-x, --no-privileges          do not dump privileges (grant/revoke)

pg_dump -O -x mydb_development | gzip > mydb-2020-09-22.bak.gz

import

psql databasename < data_base_dump

pg_restore --verbose --no-acl --no-owner --data-only -h localhost -d HaulHub_development latest.dump

Install

brew install postgresql@9.6
brew install postgresql
brew services start postgresql

psql postgres

\du
\q

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

install utils

 sudo apt-get purge postgresql-client-*
 # From: https://askubuntu.com/questions/831292/how-do-i-install-postgresql-9-6-on-any-ubuntu-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-9.6 -y

check size of each database

From: https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database

SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;

check size of each table

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

who is connected to the database?

SELECT * FROM pg_stat_activity WHERE datname = '<db_user>' ORDER BY query_start desc;

Use on Mac Os X

See projects/docker/postgres:

docker-compose up

Restart on mac os x

brew services restart postgres

Troubleshooting

sudo su postgres

psql

update pg_database set datistemplate=false where datname='template1';
drop database Template1;
create database template1 with owner=postgres encoding='UTF-8'
  lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;

update pg_database set datistemplate=true where datname='template1';

postgres=# create database template0 TEMPLATE postgres;
ERROR:  database "template0" already exists
postgres=# create database template1 TEMPLATE postgres;
CREATE DATABASE
postgres=#

another try:

UPDATE pg_database SET datallowconn = TRUE where datname = 'template0';
\c template0
UPDATE pg_database SET datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with template = template0 encoding = 'UNICODE'  LC_CTYPE = 'en_US.UTF-8' LC_COLLATE = 'C';
UPDATE pg_database SET datistemplate = TRUE where datname = 'template1';
\c template1
UPDATE pg_database SET datallowconn = FALSE where datname = 'template0';