Postgres

Connection string URL

Doc: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS

Utilidades

Instalando

APT

Instalar e habilitar:

sudo apt install postgresql postgresql-contrib -y
 
# Enable postgres service
sudo systemctl enable --now postgresql

Verificar status:

sudo systemctl status postgresql

Reiniciar banco

Caso usando systemctl, basta:

sudo systemctl restart postgresql

Criação de usuários e database

CREATE DATABASE mydatabase;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
 
-- Dá permissão sobre o banco, mas não sobre os schemas e tabelas dentro dele.
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Para dar permissão para o schema public, por exemplo, ao usuário:

GRANT USAGE ON SCHEMA public TO contratae_back;
GRANT CREATE ON SCHEMA public TO contratae_back;

Backup

A Complete Guide to PostgreSQL Backup & Recovery

https://www.enterprisedb.com/postgresql-database-backup-recovery-what-works-wal-pitr#section-7

Backup manual

To back up a PostgreSQL database, you can use the pg_dump utility. Here’s a simple way to do it:

1. Backup a Single Database

pg_dump -U your_user -W -F c -b -v -f your_backup_file.backup your_database

• -U your_user → Specifies the PostgreSQL user.

• -W → Prompts for the password.

• -F c → Uses the custom format (recommended for restoring with pg_restore).

• -b → Includes large objects (blobs).

• -v → Enables verbose mode.

• -f your_backup_file.backup → Specifies the output file.

• your_database → Replace with the database name.

2. Backup All Databases (Full Backup)

pg_dumpall -U your_user -W -f all_databases_backup.sql

• This creates a SQL file with all databases, roles, and permissions.

3. Restore a Database Backup

For a single database backup (custom format .backup file):

pg_restore -U your_user -W -d your_database -v your_backup_file.backup

For a full backup (.sql file):

psql -U your_user -W -f all_databases_backup.sql postgres

4. Automate Backups (Daily)

Add a cron job to automate backups. Open crontab:

crontab -e

Then add a line to run the backup every day at 2 AM:

0 2 * * * pg_dump -U your_user -F c -b -f /path/to/backup/your_database_$(date +\%Y\%m\%d).backup your_database

This will save backups with a timestamp (e.g., your_database_20250318.backup).

This method ensures your data is safe and restorable when needed! 🚀