Running PostgreSQL and PostGIS on WSL
I generally develop on my Mac. But, working for a state agency means that all our work issued computers are Dells. I find it nearly impossible to develop efficiently on a Windows machine. Sometimes I can't avoid it and I need to develop on the work computer. Or, more likely, I need to get my coworkers setup to run a process I've developed on my Mac on their Windows computers.
This post assumes you've already installed WSL, and Ubuntu. Use this documentation if you haven't.
From the PostgreSQL installation instructions:
- Create file repository configuration.
- Import repository signing key.
- Update package list.
- Install latest version of postgres
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get -y install postgresql postgresql-client
- Create a user and password for the default postgres user:
sudo passwd postgres. Try and make this memorable, or use a password manager.
- Close and reopen the terminal.
- Start postgres:
sudo service postgresql start
- Connect to the database:
sudo -u postgres psql
Are you doing spatial stuff? Then you probably want PostGIS too.
- Add UbuntuGIS PPA.
- Install PostGIS. Probably best to look at the list of package in the PPA to get the correct versions installed.
sudo add-apt-repository ppa:ubuntugis/ppa sudo apt-get update sudoe apt-get install postgresql-14-postgis-3 postgresql-14-postgis-3-scripts
- Connect and check the available postgres extensions:
sudo -u postgres psql
select * from pg_available_extensions where name like 'postgis%';
Create a New Postgres User
I like to add a user to postgres that matches my Ubuntu user. I think this is what common Mac installations like homebrew or the PostgresApp do. It seems silly, but it makes connecting a few less characters!
create role username with login superuser createdb createrole replication bypassrls password 'password'; \q -- quite postgres -- login to default database, shell command psql postgres
Now you don't need to use
sudo -u postgres psql every time.