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
[edit | edit source]MacOS
[edit | edit source]brew install postgres
Linux, BSD variants
[edit | edit source]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.
Basic Commands
[edit | edit source]Binaries: 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:
\dtorSELECT * 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~/.pgpassfile for automating login.
Releases (yearly)
[edit | edit source]PostgreSQL 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 | edit source]- 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