Databases/PostgreSQL
PostgreSQL, often simply Postgres, is an w:object-relational database management system (ORDBMS). PostgreSQL is w:ACID-compliant and transactional. PostgreSQL has updatable [w:[View (SQL)|views]] and w:materialized views, triggers, w:foreign keys; supports functions and stored procedures, and other expandability
Installing PostgreSQL
editMacOS
editbrew install postgres
Linux, BSD variants
editThe 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.
Basic Commands
editBinaries: psql
, pg_dump
.
- Connect to a PostgreSQL database:
psql -h localhost -U postgres -d somedb
[1] - Connect to a PostgreSQL database with a timeout of 5 seconds:
env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb
[2] - Show tables:
\dt
orSELECT * FROM pg_catalog.pg_tables;
[3] - Describe employees table:
\d employees
- Show/List schemas:
select nspname from pg_catalog.pg_namespace;
[4] - Create backup: use
pg_dump
[5] and~/.pgpass
file for automating login.
Releases (yearly)
editPostgreSQL release a new version approximately every year around October: https://en.wikipedia.org/wiki/PostgreSQL#Release_history
- October 2018 PostgreSQL 11.0 https://www.postgresql.org/about/news/1894/, https://www.postgresql.org/docs/release/11.0/
- October 2019 PostgreSQL 12.0 https://www.postgresql.org/about/news/1976/, https://www.postgresql.org/docs/release/12.0/
See also
edit- Databases: MariaDB
- AWS RDS
- SQL
- Patroni HA solution for PostgreSQL that requires etcd, Zookeeper, or Consul
- ↑ http://postgresguide.com/utilities/psql.html
- ↑ http://postgresguide.com/utilities/psql.html
- ↑ https://stackoverflow.com/questions/769683/show-tables-in-postgresql
- ↑ https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql
- ↑ https://www.postgresql.org/docs/current/static/app-pgdump.html