How to get a Postgres database in a minute

Steps with Docker

1. Install Docker

   Docker's convenience script installs it in one command. Docker describes it as meant for development environments; for a server you will keep for years, use the apt repository from the Docker install guide instead.

   $ curl -fsSL https://get.docker.com | sh
   $ sudo docker --version

2. Make a password

   Hex characters need no escaping in a connection string. Keep the password in a variable for the next step and save it somewhere safe: you will need it again.

   $ PGPASS=$(openssl rand -hex 18)
   $ echo "$PGPASS"

3. Start Postgres

   The official postgres image creates the user and the database from environment variables. POSTGRES_PASSWORD is required. From Postgres 18 the volume mounts at /var/lib/postgresql (for 17 and earlier, at /var/lib/postgresql/data). Publishing the port on 127.0.0.1 keeps it off the internet.

   $ sudo docker run --name pg -d --restart unless-stopped \
       -e POSTGRES_USER=app -e POSTGRES_DB=app \
       -e POSTGRES_PASSWORD="$PGPASS" \
       -v pgdata:/var/lib/postgresql \
       -p 127.0.0.1:5432:5432 \
       postgres:18

4. Check that it answers

   Run psql inside the container.

   $ sudo docker exec -it pg psql -U app -d app -c "select version();"

5. Give the app its connection string

   The format is postgresql://user:password@host:port/dbname, and 5432 is the default port. Put it in a .env file next to the app and keep that file out of git.

   $ echo "DATABASE_URL=postgresql://app:[email protected]:5432/app" >> .env
   $ chmod 600 .env
   $ echo ".env" >> .gitignore

6. Make a backup before you need one

   A custom-format dump is compressed and restores one object or the whole database. Copy it off the server, for example to object storage.

   $ sudo docker exec pg pg_dump -U app -F c app > app.dump
   # restore into a fresh database:
   $ sudo docker exec pg createdb -U app newdb
   $ sudo docker exec -i pg pg_restore -U app -d newdb < app.dump

Check that it works

Restart the container and look for your data. If a table you made is still there, the volume works.

$ sudo docker exec pg psql -U app -d app -c "create table hello (id int);"
$ sudo docker restart pg
$ sudo docker exec pg psql -U app -d app -c "\dt"

Docker, managed, or Uno

Docker on your own server costs nothing extra but is yours to look after. A managed Postgres from a hosting provider takes that work away for a monthly fee. On Uno the database is a managed one that the agent can create.

What the Uno database does not do

• A database needs a paid plan; the free trial computer is refused.
• It is not reachable from the internet, so you cannot point a tool on your laptop at it directly. Your apps on Uno computers in the same account can.
• A managed Postgres database runs on its own small Uno computer and counts against your plan.
• Backups: A backup is a copy of the whole disk, not a pg_dump file; the schedule is optional. Make your own pg_dump too if you want a file you can move to another host.
• You delete a database in the Uno console, not from the chat.

Common problems