I’ve been working with PostgreSQL for the last year, and the experience has honestly been amazing. However, every time I wanted to setup PostgreSQL on a machine, it was like trying to move through a labyrinth of blogs, documentations etc. I actually started using docker to run PostgreSQL in my mac, just to avoid the whole ordeal of navigating through psql and the Postgres command shell utilities.
Now, if you want to use docker for this, it’s as simple as the running the following command.
docker run --name <container-name> \
-e POSTGRES_PASSWORD=<password> \
-e POSTGRES_USER=<username> \
-d <database name>
However, sometimes installing docker just for a simple project might seem overwhelming. In which case, depending on your choice of OS, you might need to follow different steps. Here, I’ll be discussing how to install PostgreSQL on Ubuntu and MacOS. Now, there are lots of ways to do this, apart from the methods I will mention below, and feel free to follow any of them. These methods have worked for me with consistency, and I personally haven’t faced any major problems.
Installing on MacOS
For MacOS, we’ll be using the homebrew package manager. If you haven’t installed it yet, run the following command to get it.
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
Now, use the following commands to download and start PostgreSQL.
brew update && brew install postgresql
brew services start postgresql
postgres -V
So, now we have installed PostgreSQL on MacOS! However, we need to do a few more things to start using PostgreSQL properly, like creating a user, creating a database etc. We’ll get to that in a minute, let’s first wrap up on how to install PostgreSQL in Ubuntu.
Installing on Ubuntu
Even though I say Ubuntu, this should work on any Debian/Ubuntu based distributions as is. However, if you are using anything other than Ubuntu, I would suggest to reference another blog or article where the distribution/os you use has been worked with.
sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
Using PostgreSQL After Setup
If you’re on a linux system, postgres will create a system user postgres, and you will need that user to use postgres. It can be done simply by switching from your current user using sudo su - postgres
Now, to login to the PostgreSQL shell (also known as psql), you need to run psql. And that’s it! You’ll be logged into psql and you can now do the basic things to start.
First, I would suggest creating a new superuser using the command
CREATE ROLE <username> WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD '<password>';
This will create the user with your given username and password. Now, you can login to psql with this user using the command psql postgres -U <username>
Before we go any further, I think it’s prudent to mention a couple of things. Firstly, to login using psql, if you don’t give any extra parameters, psql will connect with the default database postgres with the default user. The default user will be postgres for Linux and your username of you MacOS. If you want to login using a specific user, you have to provide the database as well.
Now, I’ll mention a few commands which usually gets the starting done.
# to create a database
CREATE DATABASE <database name>;
# to access a database using a specific user
GRANT ALL PRIVILEGES ON DATABASE <database name> TO <username>;
# change user password. note that you cannot change the password of
# the user you are currently logged into
ALTER USER <username> WITH ENCRYPTED PASSWORD '<password>';
Reference and Credits
The following blogs and sites have been quite helpful for installing and working with PostgreSQL.
Top comments (0)