postgres-quick-setup-ubuntu.md

Quick setup Postgres 10 on Ubuntu 18.10

Paste this into your terminal, replacing acme with your project name:

PGUSER=acme
PGPASS=acmepass
PGDB=acmedb

sudo apt-get install postgres
sudo -u postgres createuser $PGUSER
sudo -u postgres createdb $PGDB
sudo -u postgres psql -c "alter user $PGUSER with encrypted password '$PGPASS'";
sudo -u postgres psql -c "grant all privileges on database $PGDB to $PGUSER";
sudo -u postgres psql -c "grant all privileges on database $PGDB to $PGUSER";
sudo -u postgres psql acmedb -c "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"";

sudo service postgresql restart

Sometimes you need to do pass authentication with the default user, which has no password. Let's fix that:

sudo -u postgres psql -c "alter user postgres password 'lovedata'";

Sometimes you need to connect to a database that isn't public. SSH tunnels work very well for this:

ssh -L 1111:localhost:5432 [email protected]

Test with psql:

psql -h localhost -p 1111 -U acme acmedb

Don't forget to leave this terminal open while you're using the tunnel.

Add this alias to your ~/.bashrc:

echo alias psqlacme=\"PGPASSWORD=$PGPASS psql -h localhost -U $PGUSER $PGDB\" >> ~/.bashrc
source ~/.bashrc

So that you can simply type psqlacme to quickly start playing with this new db.