The security warning you might see

Once you open Postgres.app and initialize the server, you should see some initial databases

Example of databases in the Postgres app

Follow the instructions fordownloading Postgres for Linux; we recommend using apt installation.

# Create the file repository configuration:sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key:wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists:sudo apt-get update
# Install the latest version of PostgreSQL.# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':sudo apt-get -y install postgresql
Copy to clipboard

# Create the file repository configuration:sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key:wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists:sudo apt-get update
# Install the latest version of PostgreSQL.# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':sudo apt-get -y install postgresql

You’ll then be prompted to start your server. To do so, follow the instructions provided:

sudo pg_ctlcluster 12 main start
Copy to clipboard

sudo pg_ctlcluster 12 main start

Setting up Postgres (client)

Now that Postgres is running locally, we can connect to it via a “client” – a graphical or command-line interface that enables us to connect to the Postgres server, write and executeSQL commandsas input, and see the resulting output.

While you can have only one instance of Postgres running per port (typically 5432), you can have multiple open connections to the same database with different interfaces. There are many options; our preferences are listed below!

GUI

Our preference for a cross-platform software to visually interact with Postgres isPostbird. It is open-source, simple, easy to use, and a great option for beginner and advanced users! A strong runner up isPgAdmin, one of the most popular and feature-richopen sourceadministration and development platforms for Postgres. We’ll walk you through how to install and use Postbird on different operating systems!

• Download the .exe file from thePostbird website(scroll to the bottom of the Download section).
• Open the .exe file, and disregard the Security prompt from Microsoft Defender SmartScreen by clicking on “More Info” and then “Run Anyway”. This is just confirming that you want to run an .exe file that you downloaded online.
• Postbird will automatically open, and from there you can enter in the username (postgres) and password (postgres) to connect to your locally running Postgres instance.
• From there you can now use Postbird to start writing SQL queries. You can select the database you want to work in using the dropdown menu in the top left corner, you can add tables to that database using the plus sign in the bottom left corner, and you can use the query tab to write SQL queries to manipulate and investigate tables that you create!

Postbird on Windows

• Download the application from thePostbird website.
• Open the .dmg file to install and copy the application into your Applications folder.
• Go to your Applications folder and open Postbird. You may see a Security prompt that warns “Postbird is an app downloaded from the Internet. Are you sure you want to open it?” Click “Open” to proceed. Alternatively, you may need to right-click on the application and then click “Open” the first time you open the application.
• From there you can now use Postbird to start writing SQL queries. You can select the database you want to work in using the dropdown menu in the top left corner, you can add tables to that database using the plus sign in the bottom left corner, and you can use the query tab to write SQL queries to manipulate and investigate tables that you create!

Postbird on Mac

Install viaSnap, which comes pre-installed on Ubuntu. From Terminal:

sudo snap install postbird
Copy to clipboard

sudo snap install postbird

Command Line

We’ll primarily be using Postbird as our client for connecting to Postgres, but you can also connect using the command line toolpsql!psqlis the standard interactive CLI used by both hobbyists and professionals.

According to the Postgres documentation, psql “enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file. In addition, it provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.”

When usingpsql, you can use the\qcommand to quit out ofpsqland return to your normal terminal.

Let’s look at how to download psql on different operating systems.

The EDB installation of Postgres comes pre-packaged with command line tools. Simply open the Windows Command Prompt, and execute the .exe:

Microsoft Windows [Version 10.0.19041.264](c) 2020 Microsoft Corporation. All rights reserved.
C:\Users\avinashmoondra>"C:\Program Files\PostgreSQL\12\bin\psql.exe" --username postgres
Copy to clipboard

Microsoft Windows [Version 10.0.19041.264](c) 2020 Microsoft Corporation. All rights reserved.
C:\Users\avinashmoondra>"C:\Program Files\PostgreSQL\12\bin\psql.exe" --username postgres

You’ll be prompted for the password (postgres), and then you can run a test query! (assuming you’ve created a table during the GUI installation)

psql (12.4)WARNING: Console code page (437) differs from Windows code page (1252)         8-bit characters might not work correctly. See psql reference         page "Notes for Windows users" for details.Type "help" for help.
postgres=# select * from films; id |    name    | release_date----+------------+--------------  1 | The Matrix | 1999-03-31(1 row)
Copy to clipboard

psql (12.4)WARNING: Console code page (437) differs from Windows code page (1252)         8-bit characters might not work correctly. See psql reference         page "Notes for Windows users" for details.Type "help" for help.
postgres=# select * from films; id |    name    | release_date----+------------+--------------  1 | The Matrix | 1999-03-31(1 row)

From Postgres.app, simply click on any database you’d like to connect to. It will automatically open up Terminal or iTerm (depending on your setting in Preferences) with psql CLI running.

From here, you can run a test query:

psql (12.3)Type "help" for help.
postgres=# select * from films; id |    name    | release_date----+------------+--------------  1 | The Matrix | 1999-03-31(1 row)
Copy to clipboard

psql (12.3)Type "help" for help.
postgres=# select * from films; id |    name    | release_date----+------------+--------------  1 | The Matrix | 1999-03-31(1 row)

postgresql-12 installation via apt-get comes with command-line tools.

parallels@parallels-Parallels-Virtual-Platform:~$ sudo -u postgres psql postgres[sudo] password for parallels: psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))Type "help" for help.
postgres=# 
Copy to clipboard

parallels@parallels-Parallels-Virtual-Platform:~$ sudo -u postgres psql postgres[sudo] password for parallels: psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))Type "help" for help.
postgres=#

You’ve installed, run, and connected to Postgres! We achieved this by setting up a Postgres server and client locally. Now you have the full power of the Postgres flavor of SQL at your fingertips. You’ll be able to manage all the data for any application you can dream of writing. Congratulations!

'The Codecademy Team, composed of experienced educators and tech experts, is dedicated to making tech skills accessible to all. We empower learners worldwide with expert-reviewed content that develops and enhances the technical skills needed to advance and succeed in their careers.'