Difference between revisions of "Postgres"
From Wasya Wiki
(16 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
+ | = 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 == | ||
<pre> | <pre> | ||
+ | brew install postgresql@9.6 | ||
brew install postgresql | brew install postgresql | ||
brew services start postgresql | brew services start postgresql | ||
Line 13: | Line 35: | ||
</pre> | </pre> | ||
+ | == install utils == | ||
+ | <pre> | ||
+ | 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 | ||
+ | </pre> | ||
+ | |||
+ | == check size of each database == | ||
From: https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database | From: https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database | ||
− | |||
<pre> | <pre> | ||
SELECT | SELECT | ||
Line 23: | Line 55: | ||
</pre> | </pre> | ||
− | check size of each table | + | == check size of each table == |
<pre> | <pre> | ||
SELECT | SELECT | ||
Line 30: | Line 62: | ||
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External 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; | FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | == 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 == | ||
+ | <pre> | ||
+ | |||
+ | 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'; | ||
+ | |||
+ | </pre> | ||
+ | <pre> | ||
+ | |||
+ | postgres=# create database template0 TEMPLATE postgres; | ||
+ | ERROR: database "template0" already exists | ||
+ | postgres=# create database template1 TEMPLATE postgres; | ||
+ | CREATE DATABASE | ||
+ | postgres=# | ||
+ | |||
+ | </pre> | ||
+ | |||
+ | |||
+ | another try: | ||
+ | |||
+ | <pre> | ||
+ | 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'; | ||
</pre> | </pre> |
Latest revision as of 01:59, 21 March 2022
Contents
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';