Postgres

Postgres info to keep around

Start Postgres DB

First step I used was creating a backup file from my local DB that was running.

• If using Docker, docker compose up -d will get it running.
• If using Homebrew, brew services start postgresql (Note: homebrew does want you to use a specific version where possible)

Making a backup file

Next you can dump the DB to a .sql file.

• For Docker, you have to run the command inside the docker container, so you'll need the docker exec like in the following command docker exec <containter-name> pg_dump -U <postgres-username> <postgres-db-name> > <filename>.sql
• For native development, you can simply run pg_dump -U <postgres-username> <postgres-db-name> > <filename>.sql

Copy backup file into Docker container (DOCKER ONLY STEP)

For Docker only, you need to get the .sql file into the Docker container. The docker cp command will help you do this, but first, you'll need to put the file somewhere on the docker container. Using command docker exec -it <container-name> mkdir /var/opt/backup will create a backup directory on the docker container. You can then run docker cp <filename>.sql <container-name>:/var/opt/backup to copy the file into the docker container.

Restore from SQL file

Finally, you will need to run the backup command to seed the database.

• For Docker, docker exec <container-name> psql -U <postgres-username> -f /var/opt/backup/<filename>.sql
• For native, psql -U <postgres-username> -f /path/to/file/<filename>.sql

Postgres connection string makeup

When connecting to a Postgres DB with a connection string, it will look like the following: postgresql://USER:PASSWORD@HOST:PORT/DATABASE

• USER = username for the DB user
• PASSWORD = user password for DB user
• PORT = Port it is running on (Default is 5432 for Postgres)
• DATABASE = Database name you are trying to connect to