Install And Setup PostgreSQL

Introduction

PostgreSQL, the open-source database system, integrates well with popular operating systems. To take advantage of all PostgreSQL has to offer, administrators must be able to quickly set up user permissions to access the database. During the setup process, they may encounter errors. Learn how to address these types of situations and install setup a PostgreSQL database.

Prerequisites

• Download, install and configure PostgreSQL for your OS.

Add PostgreSQL and the APT repository for Debian Linux

• If you’re using Debian Linux, run a PostgreSQL installation and APT repository update:

Install the Apache and PostgreSQL dependency packages

• Add the Ubuntu dependency packages for PostgreSQL and Apache to continue to install setup Postgresql database connect Python:

• Install Apache libraries for the server if you’re using Apache:

• Get the PostgreSQL media key with the command wget:

• Use the sudo command to access privileges and the flag -c interpreter for the shell to get the file sources.list.

• After that, install the packages for PostgreSQL as shown here:

• Now, run another APT repository update:

Get the library package for PostgreSQL 11

• If you want to skip the complete APT repository install, and perform a reduced one, add only the postgresql-11 library directly from the APT repository:

• Another alternative is to select only the generic stable version of PostgreSQL and install it. Be sure to choose the latest postgresql option.

Run a PostgreSQL reload on a Debian Linux distro

• Use the following service command to reload PostgreSQL on a Debian Linux distro.

Use Homebrew to get PostgreSQL on macOS

• In Finder, open Utilities.

• Open the application, Terminal.

• Enter the following command to install the Homebrew application:

• Now, update the Homebrew repositories:

• Use the command brew to install PostgreSQL:

• Start the PostgreSQL service:

Confirm the PostgreSQL installation

• Check that PostgreSQL is installed correctly and working with the command psql for your PostgreSQL database:

• You should see something similar to this as a response:

• Here’s how to get the Homebrew version of PostgreSQL on your macOS:

Obtain sudo privileges as a user on a UNIX system

• It’s common for Ubuntu and macOS to disable sudo. But if you don’t have privileges to access the system, use the command -su along with your username. If you’re uncertain, enter whoami as your username.

• Here’s a different way to gain admin user elevated privileges:

The command psql allows you to make a PostgreSQL connection

• Open a terminal window and enter the command psql to make a PostgreSQL connection to install and setup a PostgreSQL database. If you’re using Linux, type the sudo command along with postgres, the superuser default.

• See this example below. It illustrates how to use some_user as the username to make a table and host connection with PostgeSQL.

Here’s an example of how to use psql to connect to a host and table with the username some_user:

NOTE: The address of the IP or domain host goes directly after the option -h, and the name of the database follows -d.

Setup a new user for PostgreSQL

• At the prompt in the terminal, enter this command to create a username for the user for PostgreSQL:

• Use sudo and enter the interface psql:

• If you don’t have sudo privileges, the command should look similar to this:

NOTE: Special characters are not permitted in PostgreSQL usernames you create. Also, your username has to begin with a letter.

Addressing PostgreSQL user access errors

You might receive an unable to initialize policy plugin error or one that states Peer authentication failed for user "some_user". If you do, this means your username has permission errors associated with it.

• Overcome user access errors by finding the file containing the usernames for the PostgreSQL configuration:

• Next, use a text editor such as nano to change the file. Put the username in the file and add authentication md5 privileges:

Remove a PostgreSQL username

• When you want to remove a PostgreSQL user, use the command dropuser like this:

Access the interface for PostgreSQL

• Enter the PostgreSQL psql interface with the new username you just created:

How Windows users can enter the interface for PostgreSQL

• Go to a terminal prompt and input the following command but be sure to enter your username for -U and your local host or IP address for -h as explained earlier in this tutorial:

NOTE: As long as you use the psql command, you should be able to enter PostgreSQL.

• Make a PostgreSQL connection with the command below that shows the database name after the -U username. Before that, you have the -h local host or IP address. See the order example here:

Construct a database during your active PostgreSQL connection

• Attempt to use the postgres default username to get into psql:

• Optionally, try this:

• After you’ve gained access, create a test database and call it some_database or something else:

• Next, give database access privileges to a new user that you create. A password is also required to go along with that new user:

When you’re finished inputting the above command, a CREATE ROLE response should appear.

• Exit out by entering \q.

• Log in with the new user’s credentials, the one you just created.

The standard for PostgreSQL naming conventions is that the words should be separated with an underscore (_) for clarity in reading and all letters should be lowercase.

However, if you decide you want to use names without the underscore to separate them, you can use a space as a separator. You should capitalize the first letter of each word and surround them with quotes (") like this:

NOTE: Realize that the above method of naming databases using spaces between words is not the favored technique.

Get an entire database listing for PostgreSQL

• In the interface of psql, enter /1 to get a listing of each database that PostgreSQL has on the server:

NOTE: Be sure to check that your string has quotes that are single ('), not double. In addition, check that every command line of psql line has a semi-colon (;)at the end.

Conclusion

This tutorial explained how administrators can install setup a PostgreSQL server and database. It showed you different ways to install PostgreSQL on macOS, Linux, and Windows environments. The tutorial also addressed how to correctly set up user names and passwords. In addition, it illustrated what steps to take to fix user permission errors when they occur. Increase your productivity and lower downtime using the techniques outlined in the tutorial today.