Difference between revisions of "PostgreSQL"

From wikieduonline
Jump to navigation Jump to search
(123 intermediate revisions by 6 users not shown)
Line 1: Line 1:
 
+
[[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.
'''[[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 ==
 
== Installing PostgreSQL ==
 
=== MacOS ===
 
=== MacOS ===
*<code>[[brew]] install postgres</code>
+
* <code>[[brew install postgres]]</code>: <code>[[pg_ctl]]</code> and other 33 binaries.
 +
* <code>[[brew install libpq]]</code>: <code>[[psql]]</code> and other 28 binaries.
 +
 
 
=== Linux, BSD variants ===
 
=== 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 <code>pkg</code> command in DragonFlyBSD or <code>apt</code> 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.
 
The easiest way to install PostgreSQL in these operating systems is to use their respective built-in package managers - for instance, the <code>pkg</code> command in DragonFlyBSD or <code>apt</code> 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]]: <code>[[apt install postgresql]]</code>
 +
* [[Amazon AMI]]: <code>[[apt install postgresql-server]]</code>
 +
 +
=== Docker ===
 +
[[docker run]] -p 5432:5432 --name MY_POSTGRES_DB -e POSTGRES_PASSWORD=mysecretpassword -d postgres<ref>https://hub.docker.com/_/postgres</ref>
 +
* [[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
 +
 +
image: [[arm64v8/postgres]]
 +
 +
 +
* Binaries: [[pg_isready]], [[psql]], [[pg_restore]]
 +
 +
== PostgreSQL ==
 +
* <code>[[bitnami/postgresql]]</code>
 +
* <code>[[bitnami/postgresql-ha]]</code>
 +
 +
== Configuration and data files ==
 +
* <code>/etc/postgresql/*/main/[[postgresql.conf]]</code>
 +
* <code>/etc/postgresql/*/main/[[pg_hba.conf]]</code> (PostgreSQL Client Authentication Configuration File)
 +
 +
# "local" is for Unix domain socket connections only
 +
local  all            all                                    md5
 +
 +
* Data: <code>[[/var/lib/postgresql/]]</code>
 +
 +
== [[PostgreSQL logs]] ==
 +
*<code>[[/var/log/postgresql/]]</code>
  
 
== Basic Commands==
 
== Basic Commands==
Binaries: <code>psql</code>, <code>pg_dump</code>.
+
Binaries: <code>[[psql]]</code>, <code>[[pg_dump]]</code>.
  
* Connect to a PostgreSQL database: <code>psql -h localhost -U postgres -d somedb</code><ref>http://postgresguide.com/utilities/psql.html</ref>
+
* Connect to a PostgreSQL database: <code>[[psql]] -h localhost -U postgres -d somedb</code><ref>http://postgresguide.com/utilities/psql.html</ref>
 
* Connect to a PostgreSQL database with a timeout of 5 seconds: <code>env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb</code><ref>http://postgresguide.com/utilities/psql.html</ref>
 
* Connect to a PostgreSQL database with a timeout of 5 seconds: <code>env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb</code><ref>http://postgresguide.com/utilities/psql.html</ref>
* Show tables: <code>\dt</code> or <code>SELECT * FROM pg_catalog.pg_tables;</code><ref>https://stackoverflow.com/questions/769683/show-tables-in-postgresql</ref>
+
* [[Show databases]]:
 +
** <code>[[\list]]</code>
 +
** <code>SELECT datname FROM pg_database WHERE datistemplate = false;</code>
 +
* Show tables: <code>\dt</code> or <code>SELECT * FROM [[pg_catalog]].pg_tables;</code><ref>https://stackoverflow.com/questions/769683/show-tables-in-postgresql</ref>
 +
* [[Show users]]: <code>[[\du]]</code>
 +
* Drop DB: <code>[[dropdb]] DB_NAME</code>
 +
 
 
* Describe ''employees'' table: <code>\d employees</code>
 
* Describe ''employees'' table: <code>\d employees</code>
* Show/List schemas: <code>select nspname from pg_catalog.pg_namespace;</code><ref>https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql</ref>
+
* Show/List [[schema]]s: <code>select [[nspname]] from [[pg_catalog]].pg_namespace;</code><ref>https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql</ref>
* Create backup: use <code>pg_dump</code> <ref>https://www.postgresql.org/docs/current/static/app-pgdump.html</ref> and <code>~/.pgpass</code> file for automating login.
+
* Show version: <code>[[SELECT version();]]</code>
 +
 
 +
* Create backup: use <code>pg_dump</code> <ref>https://www.postgresql.org/docs/current/static/app-pgdump.html</ref> and <code>[[~/.pgpass]]</code> file for automating login.
 +
* Create user:
 +
** <code>[[create user]] <username></code>
 +
** ALTER USER user_name WITH PASSWORD 'new_password';
 +
* <code>[[create database]]</code>
 +
* Modify user privileges:
 +
** Privileges to create DB: <code>ALTER USER user1 CREATEDB;</code>
 +
 
 +
 
 +
* <code>systemctl [ [[systemctl status postgresql|status]] | [[systemctl stop postgresql|stop]] | [[systemctl start postgresql|start]] ] postgresql  </code>
 +
 
 +
 
 +
* <code>[[\l]], [[\du]], [[\dt]], [[\c]], [[\di]]</code>
 +
 
 +
== Processs ==
 +
* [[wal]] writer process
 +
 
 +
== Related terms ==
 +
* [[Role]], [[Schema]]
 +
* <code>[[pg_upgradecluster]]</code>
 +
* [[Adminer]]
 +
* [[Amazon Redshift]]
 +
* [[PostgreSQL monitoring]]
 +
** [[PostgreSQL statistics collector]]: <code>[[pg_stat]]*</code>
 +
** <code>[[prometheus-postgres-exporter]]</code>
 +
* [[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: <code>[[postgres]]</code>
 +
* Ansible: <code>[[community.postgresql.postgresql_query]]</code>
 +
* [[psycopg2]]
 +
* [[PostgreSQL logging]]
 +
* <code>[[yum install postgresql]]</code>
 +
* <code>[[arm64v8/postgres]]</code>
 +
* [[Azure Cosmos DB for PostgreSQL]]
 +
* [[Azure Database for PostgreSQL]]
  
== Releases (yearly) ==
+
== Activities ==
PostgreSQL release a new version approximately every year around October: https://en.wikipedia.org/wiki/PostgreSQL#Release_history
+
* [[PostgreSQL parameter tunning]]
* October 2019 PostgreSQL 12.0 https://www.postgresql.org/about/news/1976/, https://www.postgresql.org/docs/release/12.0/
+
* Read PostgreSQL - How to grant access to users? https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html
* October 2018 PostgreSQL 11.0 https://www.postgresql.org/about/news/1894/, https://www.postgresql.org/docs/release/11.0/
+
* [[Operations]]
 +
* Find which user you are using for connecting: <code>[[\conninfo]]</code>
  
 
== See also ==
 
== See also ==
* [[AWS RDS]], [[AWS Redshift]]
+
* [[Patroni]] [[HA]] solution for PostgreSQL that requires <code>[[etcd]]</code>, <code>[[Zookeeper]]</code>, or <code>[[Consul]]</code>
* <code>[[pg_dump]]</code>
+
* {{PostgreSQL extensions}}
* [[Patroni]] [[HA]] solution for PostgreSQL that requires [[etcd]], [[Zookeeper]], or [[Consul]]
+
* {{PostgreSQL}}
* {{DBs}}
 
  
 +
[[Category:PostgreSQL]]
 
[[Category:Databases]]
 
[[Category:Databases]]
  
 
{{CC license}}
 
{{CC license}}
 
Source: wikiversity
 
Source: wikiversity

Revision as of 10:46, 23 September 2024

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

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.

Docker

docker run -p 5432:5432 --name MY_POSTGRES_DB -e POSTGRES_PASSWORD=mysecretpassword -d postgres[1]
* 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
image: arm64v8/postgres


PostgreSQL

Configuration and data files

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

PostgreSQL logs

Basic Commands

Binaries: psql, pg_dump.

  • Connect to a PostgreSQL database: psql -h localhost -U postgres -d somedb[2]
  • Connect to a PostgreSQL database with a timeout of 5 seconds: env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb[3]
  • Show databases:
    • \list
    • SELECT datname FROM pg_database WHERE datistemplate = false;
  • Show tables: \dt or SELECT * FROM pg_catalog.pg_tables;[4]
  • Show users: \du
  • Drop DB: dropdb DB_NAME
  • Create backup: use pg_dump [6] 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;



Processs

  • wal writer process

Related terms

Activities

See also

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

Advertising: