PostgreSQL

wikipedia:PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS). PostgreSQL is ACID-compliant and Transactional. PostgreSQL has updatable Views and Materialized view, Triggers, Foreign key; supports functions and stored procedures, and other expandability. PostgreSQL listens by default in TCP port 5432.

Installing PostgreSQL

MacOS

• brew install postgres: pg_ctl and other 33 binaries.
• brew install libpq: psql and other 28 binaries.

Linux, BSD variants

The easiest way to install PostgreSQL in these operating systems is to use their respective built-in package managers - for instance, the pkg command in DragonFlyBSD or apt in Debian and its derivatives. Be advised that there will be different variants of PostgreSQL available for install at a given point, specially regarding version numbers and client/server side. You should decide beforehand which version suits your needs for a particular application. Furthermore, you need to make sure that you have sufficient privileges in the system to install software (properly configured sudo/doas access, for example) or your install will fail.

• Ubuntu: apt install postgresql
• Amazon AMI: apt install postgresql-server

Docker

docker run -p 5432:5432 --name MY_POSTGRES_DB -e POSTGRES_PASSWORD=mysecretpassword -d postgres
* Docker-compose.xml PostgreSQL

docker run -p 5432:5432 -d \
   --name some-postgres \
   -e POSTGRES_PASSWORD=mysecretpassword \
   -e PGDATA=/var/lib/postgresql/data/pgdata \
   -v /custom/mount:/var/lib/postgresql/data \
   postgres

PostgreSQL

bitnami/postgresql
bitnami/postgresql-ha

Configuration and data files

• /etc/postgresql/10/main/postgresql.conf
• /etc/postgresql/10/main/pg_hba.conf (PostgreSQL Client Authentication Configuration File)

# "local" is for Unix domain socket connections only
local   all             all                                     md5

Data:

• /var/lib/postgresql/

PostgreSQL logs

• /var/log/postgresql

Basic Commands

Binaries: psql, pg_dump.

• Connect to a PostgreSQL database: psql -h localhost -U postgres -d somedb
• Connect to a PostgreSQL database with a timeout of 5 seconds: env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb
• Show databases:
  • \list
  • SELECT datname FROM pg_database WHERE datistemplate = false;
• Show tables: \dt or SELECT * FROM pg_catalog.pg_tables;
• Show users: \du
• Drop DB: dropdb DB_NAME

• Describe employees table: \d employees
• Show/List schemas: select nspname from pg_catalog.pg_namespace;
• Show version: SELECT version();

• Create backup: use pg_dump and ~/.pgpass file for automating login.
• Create user:
  • create user <username>
  • ALTER USER user_name WITH PASSWORD 'new_password';
• create database
• Modify user privileges:
  • Privileges to create DB: ALTER USER user1 CREATEDB;

• systemctl [ status | stop | start ] postgresql

• \l, \du, \dt, \c, \di

Processs

• wal writer process

Related terms

• Role, Schema
• pg_upgradecluster
• Adminer
• Amazon Redshift
• PostgreSQL monitoring
  • PostgreSQL statistics collector: pg_stat*
  • prometheus-postgres-exporter
• Multi-Version Concurrency Control (MVCC)
• Index (PostgreSQL)
• Greenplum
• PostgreSQL replication
• PGO PostgreSQL Operator
• Amazon Aurora PostgreSQL Serverless
• PgBouncer connection pooler
• Amazon RDS for PostgreSQL
• Master user for RDS: postgres
• Ansible: community.postgresql.postgresql_query

Activities

• PostgreSQL parameter tunning
• Read PostgreSQL - How to grant access to users? https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html

See also

• Patroni HA solution for PostgreSQL that requires etcd, Zookeeper, or Consul
• PostgreSQL, PostgreSQL extensions, CREATE EXTENSION, List extensions (\dx)
• PostgreSQL: pg_dump, pg_restore, pgAdmin, pg_config, psql, pg_ctl, pg_isready, initdb, Role, Schema, DBeaver, Navicat, DataGrip, OmniDB, Adminer, docker-compose.xml PostgreSQL, PostgreSQL version, PostgreSQL logs, postgresql.conf, pg_hba.conf, $HOME/.pg_service.conf, Create database (PostgreSQL), Create user, createdb, GRANT, pg_stat, PostgreSQL VACUUM, EXPLAIN, pg stat activity, Autovacuum, ALTER DATABASE, PostgreSQL statistics collector, Shared buffers, EXPLAIN (PostgreSQL), EXPLAIN ANALYZE, Bitmap scan, EXPLAIN VERBOSE, EXPLAIN VERBOSE (PostgresSQL), WAL, ALTER USER, CREATE ROLE, CREATE USER, \du, show users, \l, The Statistics Collector, pganalyze, cron.schedule, Master, pg_tables, PostgreSQL replication, CREATE, SET, TOAST, PgBouncer, Restore DB, Index, meta-commands, Table, foreign table, Schema, Sequence, Views, materialized view, Table Partitioning, monitoring, PostgreSQL System Administration Functions, PostgreSQL extension, privileges, logging, PGTune, PostgreSQL parameter tunning, PostgreSQL modules, pgbench, PostgreSQL users, catalogs
• Databases: Database management, SQL, NoSQL, Wide column, PostgreSQL, MariaDB, MySQL, Derby DB, MongoDB, Cassandra, SQLite, HSQL2, H2, RocksDB, Microsoft SQL Server, DB2, Oracle Database, Memcached, Berkeley DB, Collation, SingleStore, Amazon Aurora, Graph database, Amazon DynamoDB, PrestoDB, Cache hit ratio, ACID, WAL, ARIES, DBMS, OLTP, OLAP, Database Schema, CockroachDB, Tables, Views, Apache Druid, RDMS

Text is available under the Creative Commons Attribution-ShareAlike License; additional terms may apply. By using this site, you agree to the Terms of Use and Privacy Policy.

Source: wikiversity