# 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)