PostgreSQL

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

Installing PostgreSQL

MacOS

• brew install postgres

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.

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 tables: \dt or SELECT * FROM pg_catalog.pg_tables;
• Describe employees table: \d employees
• Show/List schemas: select nspname from pg_catalog.pg_namespace;
• Create backup: use pg_dump and ~/.pgpass file for automating login.

Releases (yearly)

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

• AWS RDS, AWS Redshift
• pg_dump
• Patroni HA solution for PostgreSQL that requires etcd, Zookeeper, or Consul
• 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