Install And Setup PostgreSQL
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.
- 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:
sudo apt-get install libpq-dev python-dev
- Install Apache libraries for the server if you’re using Apache:
- Get the PostgreSQL media key with the command
Use the sudo command to access privileges and the flag
-cinterpreter for the shell to get the file
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-11library 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
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
brewto install PostgreSQL:
- Start the PostgreSQL service:
Confirm the PostgreSQL installation
- Check that PostgreSQL is installed correctly and working with the command
psqlfor 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
-sualong with your username. If you’re uncertain, enter
whoamias 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
psqlto make a PostgreSQL connection to install and setup a PostgreSQL database. If you’re using Linux, type the
sudocommand along with
postgres, the superuser default.
- See this example below. It illustrates how to use
some_useras 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
NOTE: The address of the IP or domain host goes directly after the option
-h, and the name of the database follows
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
- 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
Remove a PostgreSQL username
- When you want to remove a PostgreSQL user, use the command
Access the interface for PostgreSQL
- Enter the PostgreSQL
psqlinterface 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
-Uand your local host or IP address for
-has explained earlier in this tutorial:
NOTE: As long as you use the
psqlcommand, you should be able to enter PostgreSQL.
- Make a PostgreSQL connection with the command below that shows the database name after the
-Uusername. Before that, you have the
-hlocal host or IP address. See the order example here:
Construct a database during your active PostgreSQL connection
- Attempt to use the
postgresdefault username to get into
- Optionally, try this:
- After you’ve gained access, create a test database and call it
some_databaseor 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:
GRANT ALL PRIVILEGES ON DATABASE some_database TO some_user;
When you’re finished inputting the above command, a
CREATE ROLE response should appear.
Exit out by entering
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
/1to 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
psqlline has a semi-colon (
;)at the end.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started