# Postgres *This page contains general information about Postgres. For Postgres syntax, see [[PostgreSQL]].* - Main website: [postgresql.org](https://www.postgresql.org/) - [Downloads](https://www.postgresql.org/download/) - [Main repo](https://git.postgresql.org/gitweb/?p=postgresql.git;a=shortlog) (not on GitHub) - Written in [[C]] - [repo mirror](https://github.com/postgres/postgres) - [Online documentation](https://www.postgresql.org/docs/current/index.html) (on GitHub) - [A Brief History of PostgreSQL](https://www.postgresql.org/docs/current/history.html) - Started at [[UC Berkeley|Berkeley]]! - [PostgreSQL feature matrix](https://www.postgresql.org/about/featurematrix/) - [DigitalOcean install guide](https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart) ### Install #### (Ubuntu/Debian) Install with `apt` and daemonize with `systemd` Based off the [official install instructions](https://www.postgresql.org/download/linux/ubuntu/) from Postgres. Create the file repository configuration and import the repository signing key ```bash sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - ``` Update the package lists ```bash sudo apt-get update ``` Install the latest version of PostgreSQL. If you want a specific version, use `postgresql-12` or similar instead of `postgresql`. ```bash sudo apt-get -y install postgresql ``` Daemonize with `systemd` ```bash sudo systemctl status postgresql sudo systemctl start postgresql sudo systemctl stop postgresql sudo systemctl restart postgresql ``` #### (macOS) Install with Homebrew and daemonize with Homebrew services ```bash brew install postgresql@14 # Check that Postgres is correctly installed postgres -V # Check that it runs postgres -D $(brew --prefix)/var/postgresql@14 ``` If there was an existing install at `$(brew --prefix)/var/postgresql@14`, you may need to delete the data dir first: ```bash # Delete old PostgreSQL data dir if exists rm -rf $(brew --prefix)/var/postgres # Reinitialize the data dir # - pg_ctl is "a utility to init, start, stop, or control a PostgreSQL server" # - -D specifies the location of the database storage area, typically /usr/local/var/postgres # - `init` initializes the data dir pg_ctl -D $(brew --prefix)/var/postgres init ``` Daemonize with Homebrew services ```bash # View service status brew services info postgresql@14 # Start / stop without registering to start on boot brew services run postgresql@14 brew services stop postgresql@14 # Start / stop / restart while keeping it registered to start on boot brew services start postgresql@14 brew services kill postgresql@14 brew services restart postgresql@14 ``` If there are errors and/or `brew services info postgresql@14` shows an error status for postgresql during startup, run `postgres -D $(brew --prefix)/var/postgres` and see what comes up. It's also possible that another process has a lock on the postgres directory, which is specified in the `postmaster.pid` file in `$(brew --prefix)/var/postgres`. ### Install native client library `libpq` Do this only if it's required for a library you're using, e.g. [[Diesel]] (but not [[SeaORM]]) #### [Install `libpq` with Homebrew](https://formulae.brew.sh/formula/libpq) ```bash brew install libpq ``` #### Install `libpq` with [[apt]] ```bash sudo apt update sudo apt install libpq-dev ``` ## Setup ### Configure a PostgreSQL test user Access the default `postgres` database used for managing admin / user privileges ```bash # On macOS psql postgres # On Linux, we need to switch to the postgres user first: sudo -i -u postgres psql ``` Within psql shell, create a user kek with password sadge, and give them the privilege to create databases ```sql CREATE ROLE kek WITH LOGIN PASSWORD 'sadge'; ALTER ROLE kek CREATEDB; ``` List the roles using `\du` to confirm that user `kek` was created. Then quit with `\q`. (Linux only) Log out of the `postgres` user. ```bash exit ``` #### (Linux only) Edit `pg_hba.conf` On Linux, a Postgres configuration file must first be changed to allow logging in to a db when `db_username != os_username`. Edit `pg_hba.conf` located at `/etc/postgresql/<version>/main` ```bash cd /etc/postgresql/15/main sudo vim pg_hba.conf ``` Change ``` local all all peer ``` to ``` local all all scram-sha-256 ``` Restart PostgreSQL so it will pick up the changes ```bash sudo systemctl restart postgresql ``` #### Create an example db You should now be able to log in to the `psql` shell with the new `kek` user ```bash # Log in to the `postgres` DB, entering the password `sadge` if needed. $ psql -U kek postgres ``` Create the DB with user `kek` ```bash $ createdb example -U kek ``` Enter the DB with user `kek` ```bash $ psql -U kek example ``` Then, list relations (tables) with `\dt`, execute SQL queries e.g. `SELECT * from "user";`, etc ## General ### Default credentials for testing `postgresql://kek:sadge@localhost:5432/<appname>-dev` - Host: `localhest` - User: `kek`, as configured above - Password: `sadge`, as configured above - Port: `5432` - This is the default Postgres port. See the information associated with the `-p` (port) flag within the output of `psql --help` ### Run Postgres or view helpful info for troubleshooting ([[macOS]]) Error example ```bash $ postgres -D /usr/local/var/postgres postgres: could not access directory "/usr/local/var/postgres": No such file or directory Run initdb or pg_basebackup to initialize a PostgreSQL data directory. ``` Working example ```bash $ postgres -D /usr/local/var/postgres 2022-05-20 16:29:21.351 PDT [37180] LOG: starting PostgreSQL 14.3 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit 2022-05-20 16:29:21.352 PDT [37180] LOG: listening on IPv6 address "::1", port 5432 2022-05-20 16:29:21.352 PDT [37180] LOG: listening on IPv4 address "127.0.0.1", port 5432 2022-05-20 16:29:21.353 PDT [37180] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2022-05-20 16:29:21.357 PDT [37181] LOG: database system was shut down at 2022-05-20 16:29:14 PDT 2022-05-20 16:29:21.361 PDT [37180] LOG: database system is ready to accept connections ^C2022-05-20 16:29:22.281 PDT [37180] LOG: received fast shutdown request 2022-05-20 16:29:22.288 PDT [37180] LOG: aborting any active transactions 2022-05-20 16:29:22.288 PDT [37180] LOG: background worker "logical replication launcher" (PID 37187) exited with exit code 1 2022-05-20 16:29:22.288 PDT [37182] LOG: shutting down 2022-05-20 16:29:22.294 PDT [37180] LOG: database system is shut down ``` ### Enter database Enter database `foo` ```bash psql foo ``` Enter admin database ```bash psql postgres ``` Quit ```psql \q ``` ## Resources ### [Setting up a PostgreSQL Database on Mac](https://www.sqlshack.com/setting-up-a-postgresql-database-on-mac/) (Blog post, decent) - Worked decently (but not perfectly) well as of [[2022-05-20]] ### [PostgreSQL Tutorial](https://www.postgresqltutorial.com/) *Welcome to the PostgreSQLTutorial.com website! This PostgreSQL tutorial helps you understand PostgreSQL quickly. You’ll master PostgreSQL very fast through many practical examples and apply the knowledge in developing applications using PostgreSQL.* ## Security Resources ### [How to Secure PostgreSQL: Security Hardening Best Practices & Tips](https://www.enterprisedb.com/blog/how-to-secure-postgresql-security-hardening-best-practices-checklist-tips-encryption-authentication-vulnerabilities) (EDB blog) - Apply principles of least privilege ### [PostgreSQL Database Security: What You Need To Know](https://www.percona.com/blog/2021/01/04/postgresql-database-security-what-you-need-to-know/) (Percona blog) - Enable row-level / object level authorization Follow-up articles: - [PostgreSQL Database Security: Authentication](https://www.percona.com/blog/2021/02/01/postgresql-database-security-authentication/) - [PostgreSQL Database Security: OS – Authentication](https://www.percona.com/blog/postgresql-database-security-os-authentication/) - [PostgreSQL Security Missteps and Tips](https://www.percona.com/blog/2020/06/04/postgresql-security-missteps-and-tips/) ### [Securing Your PostgreSQL Database](https://goteleport.com/blog/securing-postgres-postgresql/) (Teleport blog) [[2022-05-01]] ## [[Rust]]-specific Resources ### Rust-Postgres - [GitHub repo](https://github.com/sfackler/rust-postgres) - [`postgres`](https://docs.rs/postgres/latest/postgres/) crate - [`tokio-postgres`](https://docs.rs/tokio-postgres) crate - [`postgres-types`](https://docs.rs/postgres-types) crate - [`postgres-native-tls`](https://docs.rs/postgres-native-tls) crate - [`postgres-openssl`](https://docs.rs/postgres-openssl) crate ### Rust Cookbook: [Working with Postgres](https://rust-lang-nursery.github.io/rust-cookbook/database/postgres.html) ### 24 days of Rust > Day 11 - Postgres - [Up to date post](https://zsiciarz.github.io/24daysofrust/book/vol1/day11.html) (but broken code examples) - [Out of date historical post](https://siciarz.net/24-days-of-rust-postgres/) (but with working code examples) - [The Postgres code source on GitHub](https://github.com/zsiciarz/24daysofrust/blob/master/vol1/src/bin/day11.rs) ### [Quickstart: Use Rust to connect and query data in Azure Database for PostgreSQL - Single Server](https://docs.microsoft.com/en-us/azure/postgresql/single-server/connect-rust) (Microsoft [[Azure]] Doc) Links: [[Azure]] ### [Setting up Rust API with Diesel and PostgreSQL](https://www.section.io/engineering-education/rust-api-with-diesel-orm-and-postgresql/) (Blog post) - *Diesel is an ORM and query builder designed to reduce the boilerplate for database interactions.* - Diesel [Getting Started guide](https://diesel.rs/guides/getting-started.html) - Diesel [Rust docs](https://docs.rs/diesel/latest/diesel/index.html) - Diesel [repo](https://github.com/diesel-rs/diesel)