How to work with postgresapp.com on macOS 👏

postgres_app_mac_os.md

PostgreSQL on macOS

Remember: Terminal app is your friend, not enemy.

Start

1. install the app
2. click on plus icon in the bottom left corner
3. create a new server: name, version, other form fields left with default values
4. click to Initialize
5. now you have default databases
6. configure your $PATH for CLI commands sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp or sudo vim ~/.bash_profile and add this line: export PATH="/Applications/Postgres.app/Contents/Versions/15/bin:$PATH" & save, restart terminal
7. 👏

Create db

1. open your terminal app (for example iTerm)
2. configure your $PATH to use the included command line tools (read the point 3)
3. write psql and submit
4. now you should see something like: psql (14.6 (Homebrew), server 15.1) which is basically welcome message
5. write: create database hello;¹
6. write \l to list all databases, is it here? ²
7. 👏

Backup db

1. open your terminal
2. run pg_dump --dbname=hello --file=hello_backup_2023.sql --schema=public --inserts --username=postgres --host=localhost
3. check your current folder for this new file
4. 👏

Import db

You did backup of db via pg_dump and now it's time to restore this dump to your local db.

1. open your terminal
2. locate your dump file
3. write pg_restore -U postgres -Fc -d hello < /Users/landsman/Downloads/aurora_dump.sql
4. if you see any error with input, try to check the -F parameter ³
5. 👏

Remove db

be careful there is no confirm dialog!

1. open your terminal
2. write psql and submit
3. write drop database hello; and submit ¹
4. write \l to list all databases, is it gone?
5. 👏

Manage data

To easily manage your database check out DataGrip. It have the best UX on the market. Nice to have features: CSV export & import, tree visualisation.

To configure paths for CLI commands like pg_dump use CLI tool which, so for example: which pg_dump should give you something like: /Applications/Postgres.app/Contents/Versions/15/bin/pg_dump.

Footnotes

1. Note: be sure to always end your SQL statements with ;. ↩ ↩²

2. All avaliable CLI tools are listed here. ↩

3. "The input is a PostgreSQL custom-format dump." or "input file does not appear to be a valid archive" ... mostly this error mean that your restore action used invalid format. Check format -F (--format=) argument. Values are: c|d|t|p ... (custom, directory, tar, plain text (default)) ↩

postgres_ubuntu.md

PostgreSQL on Ubuntu

or other distributions based on Debian/Ubuntu.

Remember: Terminal app is your friend, not enemy.

⚠️ this doc is WIP

Start

1. install docker if you didn't have it already. Follow documentation.
2. download image docker pull postgres
3. initial setup sudo -u postgres -i psql
   1. set your password for super user by: ALTER USER postgres WITH PASSWORD 'root'; Don't forget ; at the end.
   2. check if there is ALTER ROLE as successful response 👏
   3. exit with \q.