Linux install postgresql client

PostgreSQL Tips: Installing the PostgreSQL Client

Install only the necessary tools for a lean, mean PostgreSQL client machine

Why install all of PostgreSQL when your database server is up in the cloud, managed as an IBM Cloud Database for PostgreSQL?

Most instructions for installing the PostgreSQL tools assume you want the database installed too. It’s a reasonable assumption if you’re dealing with users who don’t have access to PostgreSQL in the cloud or on a remote server.

In this PostgreSQL Tip, we’ll look at how you can install just psql on Windows, Linux, and macOS, allowing you to quickly get up and running and connected to your IBM Cloud Databases (ICD) for PostgreSQL service. We’ll cover the following:

• macOS with Homebrew
• Ubuntu 16.04 and 18.04
• Red Hat Enterprise Linux 7 (and others)
• Fedora 27 and 28
• Windows 10

We recommend Homebrew as a package manager for macOS. With Homebrew in place, you’ll be able to install numerous applications, usually with the programs available in /usr/local/bin . Homebrew’s package for the PostgreSQL client tools is the libpq package. Brew makes it easy to install:

There’s a small catch though—libpq won’t install itself in the /usr/local/bin directory. To make that happen, you need to run:

This will symlink all the tools (not just libpq) into the /usr/local/bin directory. You’re now ready to run psql and start connecting.

Linux systems (unlike macOS) have a package manager built in. For Ubuntu (and Debian-based distributions) thats’s the apt command. The PostgreSQL client is distributed in the appositely named postgresql-client , so all you need to do is run the following:

sudo apt-get install postgresql-client

This will install the PostgreSQL 10 client, which can easily connect to earlier versions of PostgreSQL.

Red Hat Enterprise Linux 7 (and others)

For Red Hat Enterprise Linux (RHEL), there’s a little more set up to do than with Ubuntu. For RHEL, the package manager is yum . First, you need to point yum at the PostgreSQL repository like this:

sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat10-10-2.noarch.rpm

Yum goes to that URL and configures itself to use that package repository. With that done, you can add packages from it by name:

sudo yum install postgresql10

This installs just the client packages. If you are wondering where to find that repository URL, head to Linux Downloads (Red Hat Family). You’ll find a form which will let you select the PostgreSQL version, platform, and architecture, and it’ll give you the appropriate instructions for that Red Hat variant—that includes CentOS, Scientific Linux, and Oracle Enterprise Linux. It also includes Fedora.

Fedora’s default repositories already have a PostgreSQL client available from them. So, for Fedora 27, 28, and later, install the PostgreSQL client from the terminal with:

sudo dnf install postgresql.x86_64

And that’s all you need to do!

On Windows 10, there’s a bit more work to be done. We currently recommend using the PostgreSQL installer from Enterprise DB. It’s a full installation package for PostgreSQL on Windows but you can set it to only install the command line tools like psql. Click on that link and select PostgreSQL 9.6 or 10 and Windows 10 as the platform. Once the executable file is downloaded, run it and you’ll see the following options:

Select only the Command Line Tools because we don’t need the server installed. After it installs, you’ll need to set up your Windows environment variables so that you can use the psql client in the command prompt.

Go to the Control Panel > System and Security > System and select Advanced system settings.

From there, you’ll see a box called System Properties. Select Environment Variables. A window will appear with the two sets of environment variables. In the top set (marked User variables for…), select the PATH entry and then click the Edit button. An edit window will appear. Click New and add the path to the psql client. Your path will depend on where PostgreSQL installed, but typically that would be:

After that, click OK a couple of times to go back to the desktop. Start a new Command Prompt, and you should be able to run psql. With no server set and no server running locally, you’ll see the following, which means that it’s working:

Now that we’ve covered getting set up with the psql client for PostgreSQL, you can start connecting to IBM Cloud Databases for PostgreSQL without having to install PostgreSQL itself on your local machine. In future PostgreSQL Tips, we’ll cover some of the basic features and functions of the database using your new psql client.

Источник

Linux downloads (Ubuntu)

PostgreSQL is available in all Ubuntu versions by default. However, Ubuntu «snapshots» a specific version of PostgreSQL that is then supported throughout the lifetime of that Ubuntu version. Other versions of PostgreSQL are available through the PostgreSQL apt repository.

PostgreSQL Apt Repository

If the version included in your version of Ubuntu is not the one you want, you can use the PostgreSQL Apt Repository. This repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.

The PostgreSQL Apt Repository supports the current versions of Ubuntu:

on the following architectures:

• amd64
• arm64 (18.04 and newer; LTS releases only)
• i386 (18.04 and older)
• ppc64el (LTS releases only)

To use the apt repository, follow these steps:

# 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

For more information about the apt repository, including answers to frequent questions, please see the PostgreSQL Apt Repository page on the wiki.

Included in distribution

Ubuntu includes PostgreSQL by default. To install PostgreSQL on Ubuntu, use the apt-get (or other apt-driving) command:

apt-get install postgresql-12

The repository contains many different packages including third party addons. The most common and important packages are (substitute the version number as required):

postgresql-client-12	client libraries and client binaries
postgresql-12	core database server
libpq-dev	libraries and headers for C language frontend development
postgresql-server-dev-12	libraries and headers for C language backend development

Copyright © 1996-2023 The PostgreSQL Global Development Group

Источник

How to Install PostgreSQL 15 on Ubuntu 22.04 Step-by-Step

In this article, we will explain how to install PostgreSQL 15 database server on Ubuntu 22.04 (Jammy Jellyfish).

PostgreSQL is a powerful, open-source object-relational Database Management System (DBMS). It’s been battle-tested for over 35 years which has earned it a strong reputation for reliability and performance. This feature-rich database is used by many tech giants, such as Apple, IMDB, Instagram, and so on.

PostgreSQL supports large number of the SQL standard and is constructed to be extensible by users in many aspects. Some of the salient features include ACID transactions, foreign keys, subqueries, triggers, user-defined types, functions, etc.

Prerequisites

Before installing the PostgreSQL server, we must ensure that the system meets the following installation requirements:

• Pre-Installed Ubuntu 22.04
• A regular user with sudo rights
• An active internet connection
• At least 2 GB of RAM with an additional 512 MB of disk space. Please note that this is a minimal requirement for the demo environment. The actual hardware configuration will vary with data volume.

Without any further delay, let’s deep dive into PostgreSQL 15 installation steps,

1) Enable PostgreSQL Package Repository

PostgreSQL 15 package is not available in the default package repository, so enable its official package repository using following commands.

$ 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 -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null

To begin, let’s fetch the latest versions of the packages. We can achieve this using the apt update command as shown below:

The above command will take a few seconds to complete.

2) Install PostgreSQL 15 Database Server and Client

The postgresql package installs the default version of the PostgreSQL database server whereas the postgresql-client package installs the client utility.

Let’s install the PostgreSQL client and server using the below apt command:

$ sudo apt install postgresql postgresql-client -y

Next, let’s verify that the PostgreSQL service is up and running:

$ sudo systemctl status postgresql

Finally, check the PostgreSQL version using the psql command line utility:

Here, we can see that the version of PostgreSQL is 15.

3) Update PostgreSQL Admin User Password

By default, we can connect to the PostgreSQL server without using any password. Let’s see this in action using the psql utility:

$ sudo -u postgres psql postgres=#

In the above output, the postgres=# prompt indicated the active connection with the PostgreSQL server.

In this example, we have used the postgres user. This is an admin user of PostgreSQL and it gets created during the installation process.

Allowing administrative access to the database without any password isn’t a good idea. So, let’s set the password for the postgres user:

postgres=# ALTER USER postgres PASSWORD 'demoPassword';

The above SQL query sets the user password to demoPassword . Please note that, we have used a very simple password because this is a demo environment. However, the same is not recommended in the production environment.

Let’s verify that the password has been set successfully. So first, terminate the current session with the server using the \q command .

Now, let’s connect to the database server again:

$ psql -h localhost -U postgres

Let’s enter the demoPassword string as a password and now we are connected to the database.

4) Configure PostgreSQL to Allow Remote Connections

By default, PostgreSQL accepts connections from the localhost only. However, we can easily modify the configuration to allow connection from remote clients.

PostgreSQL reads its configuration from the postgresql.conf file which is located in the /etc/postgresql//main/ directory. Here, the version indicates the major version of PostgreSQL.

For example, in our case the full path of the file is /etc/postgresql/15/main/postgresql.conf .

Now, open the postgresql.conf file in a text editor, uncomment the line that starts with the listen_addresses , and replace ‘localhost’ with ‘*’.

This setting is located under the CONNECTIONS AND AUTHENTICATION section. After modification the file will look like this:

Next, edit the IPv4 local connections section of the pg_hba.conf file to allow IPv4 connections from all clients. Please note that this file is also located in /etc/postgresql/15/main/ directory.

$ sudo vi /etc/postgresql/15/main/pg_hba.conf

After modification the file will look like this:

In the above configuration indicates to allow connection from the network 192.168.1.0/24

In case, Ubuntu firewall is running on your system then allow PostgreSQL 5432 port using following command,

Verifying Remote Connection

Finally, restart the service and verify it’s up and running:

$ sudo systemctl restart postgresql $ sudo systemctl status postgresql

Now, let’s try to access DB from remote client.

$ psql -h 192.168.1.192 -U postgres

In this example, 192.168.1.192 is the IP address of the PostgreSQL database server.

Here we can see that we are able to access DB from the remote client.

That’s all from this article.Please do post your queries and feedback in the below comments section.

Источник