PostgreSQL is used by many large companies to save and store their data for various applications and it supports various programming interfaces as well as videos, texts, and images. In this article we’ll first go through the installation of PostgreSQL on ubuntu 20.04 then we’ll configure it to allow remote connection

Prerequsities

This article assumes that you already have running a PostgreSQL server on your system. If not, use one of the below links to install the PostgreSQL database server on your system.

1. Installing PostgreSQL on Ubuntu 20.04
2. Installing PostgreSQL on CentOS 8

Find Configuration File

In order to install PostgreSQL on our system we need to update our repository and for that execute the below command:

sudo -u postgres psql -c "SHOW config_file;" 

Output:
               config_file
-----------------------------------------
 /etc/postgresql/13/main/postgresql.conf
(1 row)

You need to change the listening address in the postgresql.conf configuration file showing in the command output. Also, you need to edit “pg_hba.conf” in the same directory to allow remote access.

Configure PostgreSQL to Allow Remote Connections

In order to allow all the IP addresses to connect to the PostgreSQL server, we need to configure the file and make some changes, for that you have located the configuration file in the previous step.

Configuring postgresql.conf:

Now we need to open the file and make some changes in order to allow a remote connection. To open the file you’ve to use the keyword “nano” or you can run the command in the terminal that is provided below:

sudo nano /etc/postgresql/13/main/postgresql.conf 

This command will open this file and in it, you need to search “listen_addresses” and add the following line.

#listen_addresses = 'localhost'
listen_addresses = '*'



All you’ve to do is change the listening address from localhost to “*” or add a new line as shown above. This will allow every IP address to be connected to the database server, or if you want to specify some particular ips you can type them with spaces between each IP address.

Configuring pg_hba.conf:

In order to allow the users that we want to be connected to the database then we need to make changes in the “pg_hba.conf” file. This file will be available under the same directory as above.

Now open the file using the command provided below:

sudo nano /etc/postgresql/13/main/pg_hba.conf 

In the file you’ve to add the following lines in file:

# TYPE  DATABASE	USER	ADDRESS   	METHOD
host    all     	all     0.0.0.0/0       md5
host    all             all     :/0             md5



Save the configuration file and close it.

Restart Service :

Now, restart the database service to apply changes by executing the below-mentioned command:

sudo systemctl restart postgresql 

Now simply open the port “5432” in the firewall and you’re all set to see all the databases and you can bond from whichever ip address to the server of PostgreSQL:

sudo ufw allow 5432 

That’s it. Your PostgreSQL database server is accessible from remote hosts.

Conclusion

PostgreSQL database is default set to bond with localhost which restricts the other IP address and host to connect or have the access to the PostgreSQL server. In this article, we guided you through the configuration of PostgreSQL to allow remote connection so that other IPs can bond to the server. In this way, other hosts can easily see the list of databases and connect to the PostgreSQL server remotely.

The post How To Configure PostgreSQL to Allow Remote Connections appeared first on TecAdmin.

As of today, PostgreSQL 13.3 is the latest stable version available for installation. PostgreSQL version 9.5 and lower versions are no more supported by the team.

In this tutorial, you will learn, how to check the PostgreSQL version on your system.

How to Find PostgreSQL Version using Command line

Use one of the following methods to check the PostgreSQL version via the command line.

1. Check Version with Login

   Once you login to the PostgreSQL server via terminal, The post login screen displays the PostgreSQL version you have connected to.

   psql 
   

   Output:
   psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1))
   Type "help" for help.
   

The above output displays, that you are running version 13.3 of PostgreSQL server.

2. Check Version with version() Function

   You can also execute version() function to find out the PostgreSQL version. This method can also help you to check PostgreSQL version in automation scripts.

   SELECT version();
   

   Output:
                                            version
   ------------------------------------------------------------------------------------
    PostgreSQL 13.3 (Ubuntu 13.3-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc
   (1 row)
   

3. Check Version with Parameters

   Alternatively, you can also find the PostgreSQL version by the preset parameters, and you can also use this method for automatic version checks.

   SHOW server_version;
   

   Output:
             server_version
   ----------------------------------
    13.3 (Ubuntu 13.3-1.pgdg20.04+1)
   (1 row)
   

Check the below screenshot for all commands in action to check the Postgres version on command line.

How to Find Postgres Version in pgAdmin4

pgAdmin4 is an excellent web interface for managing the PostgreSQL servers. So the pgAdmin4 users can also find the Postgres version on the web interface.

To get the version, follow:

1. Login to pgAdmin4
2. In the left sidebar, expand the Servers and select your Postgres server
3. Go to Properties tab
4. Under the General section, check for PostgreSQL version.

Conclusion

In this tutorial, you have learned 3 methods to check the PostgreSQL version via the command line. Also provided the steps to find version in pgAdmin4.

The post How to Check the PostgreSQL Version appeared first on TecAdmin.

• Installing pgAdmin4 on Debian 10

Step 1 – Setup PostgreSQL PPA

First, you need to import PostgreSQL packages signing key on your system. Use the below command to import the key.

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

Now add PostgreSQL apt repository in your system as per your operating system. These are suggested on official PostgreSQL website using following command.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Step 2 – Install PostgreSQL on Debian 10

At this stage, you have successfully added PostgreSQL official repository in your system. Now update the repository list. After that install Latest PostgreSQL Server in our Ubuntu system using following commands.

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Step 3 – Connect to PostgreSQL

After installing the PostgreSQL database server by default, it creates a user ‘postgres’ with role ‘postgres’. It also creates a system account with the same name ‘postgres’. So to connect to postgres server, login to your system as user postgres and connect the database.

sudo -u postgres psql

psql (11.4 (Debian 11.4-1.pgdg100+1))
Type "help" for help.

postgres=#

Now you are logged in to PostgreSQL database server. To check login info use following command from the database command prompt.

postgres-# \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

To disconnect from PostgreSQL database command prompt just type below command and press enter. It will return you back to Debian command prompt.

postgres-# \q

Step 4 – Conclusion

Your PostgreSQL installation has been completed successfully on your Debian 9 system.

Let’s move to pgAdmin4 installation on Debian 10 systems.

The post How to Install PostgreSQL 11 on Debian 10 (Buster) appeared first on TecAdmin.

This tutorial will guide you through installation of pgAdmin4 on Debian Linux system.

Prerequisites

You must have PostgreSQL server installed on your system via an official apt repository. If you don’t have installed PostgreSQL, use below link to install.

Install PostgreSQL on Debian 9

Install pgAdmin4 on Debian

pgAdmin4 packages are available under PostgreSQL official apt repository. We assume you already have configured apt repository during the installation of the database server.

Execute below command on the terminal to begin pgAdmin4 installation on Debian.

sudo apt-get install pgadmin4 pgadmin4-apache2

The package pgadmin4-apache2 will integrate pgAdmin4 with Apache2 web server.

During the installation, you are required to setup pgAdmin4 admin login.

Enter an email address to use as admin login id for your pgAdmin4 web interface. You can use any address of your choice.

Then click OK.

Now this will prompt to input password for the administrator account.

This will complete the installation of pgAdmin4 on your system. This will also make required changes on your local PostgreSQL system for connection.

Now, pgAdmin4 is ready to use. Let’s connect pgAdmin4 with PostgreSQL database.

Configure pgAdmin4 with PostgreSQL

You have successfully installed pgAdmin4 on your Debian system. Now you can access pgAdmin4 in your favorite web browser.

Use servers IP address or domain name followed by /pgAdmin4 as subdirectory URL.

http://example.com/pgAdmin4

After successful login, you will be redirected to pgAdmin4 dashboard. As this is a new installation, there will be no servers connected.

Now, connect your first PostgreSQL instance by clicking on “Add New Server“.

This will open a popup window. This tutorial is using the basic settings to connect the database instance. Enter the user-friendly name for the new instance connection under the General tab.

Then switch to Connection tab. Enter hostname or IP address of your PostgreSQL instance. In my case, it’s running on localhost system, so I just the put localhost as the hostname. Then enter the username and password for PostgreSQL authentication.

After that click Save button.

After successful adding new instance, you will see an instance in the left sidebar. Here you can manage the database instance.

Conclusion

You have installed pgAdmin4, a web-based interface for the PostgreSQL server management. Now connect as many as PostgreSQL instances with the pgAdmin4.

The post How To Install pgAdmin4 on Debian 10/9 appeared first on TecAdmin.

pgAdmin4 is a client application used for managing PostgreSQL servers graphically. It is available as a desktop application for Linux desktop systems as well as a web application. Which provides a beautiful and easy-to-use web interface and helps us to increase productivity.

This article will help you to install the PostgreSQL server on Fedora 34/33 Linux system. Also, provide you the instructions to install pgAdmin4 on the Fedora system. This article has been tested with the Fedora version 34.

Step 1 – Installing PostgreSQL on Fedora

Follow the below instructions to install the PostgreSQL server on a Fedora system.

1. First of all, search for the available DNF modules contains the PostgreSQL server packages. Open a terminal and type the below command:

sudo dnf module list postgresql 

2. In the above command, you will see the available PostgreSQL versions. Now enable the repository of the required version. The below command will enable the repository for the PostgreSQL 13 on your system.

sudo dnf module enable postgresql:13 

3. Once you have successfully enabled the required DNF module. Use the following command to install the PostgreSQL server packages using DNF package manager. This will also install additional required packages on your system.

sudo dnf install postgresql-server 

Press ‘y’ to confirm and finish the packages installation.

4. After that, you need to initialize the PostgreSQL data directory. In other words, this will create a data directory and other configuration files on your system.

To initialize the database server, type:

sudo postgresql-setup --initdb 

After finishing the above command, the PostgreSQL server installation is completed on your Fedora system. PostgreSQL server uses PGDATA environment variable to contain the data directory location.

Setp 2 – Manage PostgreSQL Service

To start the PostgreSQL service use the following command as per your operating systems. Also, enable the PostgreSQL service to autostart on system boot.

sudo systemctl enable postgresql 
sudo systemctl start postgresql 

You can see the current status of the service using the following command.

sudo systemctl status postgresql 

Step 3 – Secure Postgres User

After completing the above steps. Your PostgreSQL 11 server is ready to use. Log in to the postfix instance to verify the connection.

su - postgres -c "psql" 

You may create a strong password for the “postgres” account to enhance server security.

postgres=# \password postgres

Step 4 – Installing pgAdmin4 on Fedora

The pgAdmin4 is a great tool for managing PostgreSQL server databases with a graphical interface. It is available as an application for desktop systems. Also, a web version is available to access in a web browser. The below steps will help you to set pgAdmin4 on your Fedora system.

1. First, configure the pgAdmin4 repository to your Fedora system. In a terminal execute the following command.

sudo rpm -Uvh https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-fedora-repo-2-1.noarch.rpm 

2. After that, install the pgadmin4-web package on your system. This will also add other dependencies to your system.

sudo dnf install pgadmin4-web 

3. The policycoreutils-python-utils package contains the management tools use to manage an SELinux environment for the Python applications. Install it on your system with the following command.

sudo dnf install policycoreutils-python-utils 

4. Finally, run a shell script to configure the pgAdmin4 on your system. This will prompt for an email address and password to enable web application authentication.

sudo /usr/pgadmin4/bin/setup-web.sh 

Remember that, this authentication is just for login to the pgAdmin4 dashboard. It can’t be used to log in to the PostgreSQL server.

Step 5 – Allow Public Access to pgAdmin4

The Fedora systems use a firewall to protect the server from external users. You need to allow access on port 80 to access pgAdmin4 from outside of the server.

To open web port in firewall, type:

sudo firewall-cmd --add-port=80/tcp --permanent 
sudo firewall-cmd --reload 

As the Fedora system has default SELinux in enforcing mode, you need to apply SELinux setting to allow network access to the webserver.

sudo setsebool -P httpd_can_network_connect 1 

In the end, restart the Apache service to reload all the settings and changes.

sudo systemctl restart httpd 

Step 6 – Access pgAdmin4

Now, you can access the pgAdmin4 web application in a web browser. Access your server with the IP address following with /pgadmin4

http(s)://server-ip/pgadmin4

Enter login credentials created in the above step. After a successful login, you will get access to the pgAdmin4 dashboard. Here you can connect multiple PostgreSQL servers and manage them.

Conclusion

In conclusion, You have successfully installed the PostgreSQL server on the Fedora Linux system. Additionally, provides you instructions to install the pgAdmin4 dashboard to manage the PostgreSQL server graphically.

The post How To Install PostgreSQL and pgAdmin4 in Fedora 35/34 appeared first on TecAdmin.

This article has been tested on CentOS Linux release 7.5

Step 1 – Configure Yum Repository

Firstly you need to configure the PostgreSQL repository in your system. Use one of the below commands as per your operating system version.

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

For more details visit PostgreSQL repositories link page where you can get repository package rpm for various operating systems.

Step 2 – Installing PostgreSQL on CentOS 7

After enabling PostgreSQL yum repository in your system use following command to install PostgreSQL 11 on your system with yum package manager.

yum install postgresql11-server

This will also install some additional required packages on your system. Enter y to confirm and complete the installation process.

Step 3 – Initialize PGDATA

After that, you need to initialize the PostgreSQL instance. In other words, this will create a data directory and other configuration files on your system. To initialize the database use the below command.

/usr/pgsql-11/bin/postgresql-11-setup initdb

The above command will take some time to initialize PostgreSQL first time. PGDATA environment variable contains the path of data directory.

PostgreSQL 11 default data directory location is /var/lib/pgsql/11/data

Setp 4 – Start PostgreSQL Server

To start PostgreSQL service using the following command as per your operating systems. Also, enable PostgreSQL service to autostart on system boot.

systemctl enable postgresql-11.service
systemctl start postgresql-11.service

Step 5 – Verify PostgreSQL Installation

After completing the above all steps. Your PostgreSQL 11 server is ready to use. Log in to your server to verify the connection.

su - postgres -c "psql"

psql (11.0)
Type "help" for help.

postgres=# 

You may create a password for user postgres for security purposes.

postgres=# \password postgres

In conclusion, You have successfully installed the PostgreSQL database server on CentOS/RHEL 7 system.

The post How to Install PostgreSQL 11 on CentOS/RHEL 7/6 appeared first on TecAdmin.

Step 1 – Add Postgres Yum Repository

The first step is to install PostgreSQL repository in your system, Use one of below commands as per your system architecture and operating system.

## CentOS/RHEL - 7
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

## CentOS/RHEL - 6
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-6-x86_64/pgdg-redhat10-10-2.noarch.rpm

For more details visit PostgreSQL repositories link page where you can get repository package rpm for various operating systems.

Step 2 – Install PostgreSQL 10 Server

After enabling PostgreSQL yum repository in your system use following command to install PostgreSQL 10 on your system with yum package manager.

yum install postgresql10-server postgresql10

Step 3 – Initialize PGDATA

After installing PostgreSQL server, It’s required to initialize it before using the first time. To initialize database use below command.

/usr/pgsql-10/bin/postgresql-10-setup initdb

Above command will take some time to initialize PostgreSQL first time. PGDATA environment variable contains the path of data directory.

PostgreSQL data directory Path: /var/lib/pgsql/10/data/

Setp 4 – Start PostgreSQL Server

To start PostgreSQL service using the following command as per your operating systems. Also, enable PostgreSQL service to autostart on system boot.

For CentOS/RHEL 7 and Fedora

systemctl start postgresql-10.service
systemctl enable postgresql-10.service

For CentOS/RHEL 6

service postgresql-10 start
chkconfig postgresql-10 on

Step 5 – Verify PostgreSQL Installation

After completing above steps, you have installed PostgreSQL 10 on your server, Let’s log in to postfix to verify that installation completed successfully.

su - postgres -c "psql"

psql (10.0)
Type "help" for help.
postgres=#

You may create a password for user postgres for security purpose.

postgres=# \password postgres

Congratulation’s! You have successfully installed PostgreSQL Server. Read below article to install phpPgAdmin.

How to Install phpPgAdmin on CentOS using Yum

Thanks for using this tutorial for installing PostgreSQL 10 on CentOS/RHEL 7/6 and Fedora 28/27/26 systems.

The post How to Install PostgreSQL on CentOS/RHEL 7 appeared first on TecAdmin.

• How to Install PostgreSQL on Ubuntu

Step 1 – Prerequsities

First, you need to import PostgreSQL packages signing key on your system. Use the below command to import the key.

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

Now add PostgreSQL apt repository in your system as per your operating system. These are suggested on official PostgreSQL website using following command.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Step 2 – Install PostgreSQL on Debian 8

At this stage, you have successfully added PostgreSQL official repository in your system. Now update the repository list. After that install Latest PostgreSQL Server in our Ubuntu system using following commands.

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Step 3 – Connect to PostgreSQL

After installing the PostgreSQL database server by default, it creates a user ‘postgres’ with role ‘postgres’. It also creates a system account with the same name ‘postgres’. So to connect to postgres server, login to your system as user postgres and connect database.

sudo su - postgres
psql

Now you are logged in to PostgreSQL database server. To check login info use following command from database command prompt.

postgres-# \conninfo

To disconnect from PostgreSQL database command prompt just type below command and press enter. It will return you back to Debian command prompt.

postgres-# \q

Your PostgreSQL installation has been completed successfully. Let’s move to phpPgAdmin installation of Ubuntu systems.

The post How to Install PostgreSQL 11 on Debian 8 (Jessie) appeared first on TecAdmin.

• Recommended => Install pgAdmin4 on Ubuntu

Step 1 – Enable PostgreSQL Apt Repository

PostgreSQL packages are also available in default Ubuntu repository. So you need to add PostgreSQL apt repository to your system suggested on official PostgreSQL website using following command.

Start with the import of the GPG key for PostgreSQL packages.

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Now add the repository to your system.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Step 2 – Install PostgreSQL on Ubuntu

Now as we have added PostgreSQL official repository in our system, First we need to update the repository list. After that install Latest PostgreSQL Server in our Ubuntu system using the following commands.

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Multiple other dependencies will also be installed. PostgreSQL 12 is the latest available version during the last update of this tutorial.

Step 3 – Create User for PostgreSQL

By default, PostgresQL creates a user ‘postgres’ with the role ‘postgres’. It also creates a system account with the same name ‘postgres’. So to connect to Postgres server, log in to your system as user postgres and connect the database.

sudo su - postgres
psql

Now configure PostgreSQL to make is accessible by your normal users. Change your_username with your actual user already created on your Ubuntu system.

postgres-# CREATE ROLE your_username WITH LOGIN CREATEDB ENCRYPTED PASSWORD 'your_password';
postgres-# \q

Then switch to the user account and run createdb command followed by the database name. This will create a database on PostgreSQL.

su - your_username 
createdb my_db

After that connect to the PostgreSQL server. You will be logged in and get database prompt. To list all available databases use these commands.

psql

rahul=> \list
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 my_db     | rahul    | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres

To disconnect from PostgreSQL database command prompt just type below command and press enter. It will return you back to the Ubuntu command prompt.

postgres-# \q

Conclusion

Your PostgreSQL installation has been completed successfully. Let’s move to install Graphical user interface for PostgreSQL like pgAdmin4 and phpPgAdmin of Ubuntu systems.

The post How to Install PostgreSQL on Ubuntu 18.04 & 16.04 LTS appeared first on TecAdmin.

This article will describe various ways to use of pg_dump command to backup database. Also you will learn how to restore postgres database backup via command line.

Backup and Restore Database in Postgres

Below is some connections options which you can use for connecting remote server or authenticated server with all queries given in this article.

• -d, --dbname=DBNAME database name
• -h, --host=HOSTNAME database server hostname or ip
• -p, --port=PORT database server port number (default: 5432)
• -U, --username=NAME connect as specified database user
• -W, --password force password prompt
• --role=ROLENAME do SET ROLE before dump

1. PostgreSQL Backup Single Database

• Backup a single database in PostgreSQL server. Use “-d” comamnd line option to provide database name to pg_dump command. Make sure to replace your actual database name in place of mydb.

  pg_dump -h localhost -U postgres -W -d mydb > mydb.sql 
  

• Restore a single database from backup in PostgreSQL. Just use “psql” command to restore PostgreSQL database.

  psql -h localhost -U postgres -W -d mydb < mydb.sql 
  

2. PostgreSQL Backup All Databases

• Backup all databases in PostgreSQL using pg_dumpall utility.

  pg_dumpall -h localhost -U postgres -W > alldbs.sql 
  

• Restore: all database backup using following command.

  psql -h localhost -U postgres -W < alldbs.sql 
  

3. PostgreSQL Backup Single Table

• Backup: a single table named mytable from mydb database.

  pg_dump -h localhost -U postgres -d mydb -W -t table_1 > mydb-table_1.sql 
  

• Restore: single table backup to database. Make sure your backup file contains only single table backup which you want to restore.

  psql -h localhost -U postgres -W -d mydb < mydb-table_1.sql 
  

4. Compressed Backup and Restore Database

• Backup PostgreSQL database in compressed format. Use gzip command line utility to accept piped backup data and make archive file.

  pg_dump -h localhost -U postgres -W -d mydb | gzip > mydb.sql.gz 
  

• Restore database from compressed backup file directly. Here gunzip will extract backup file and send data to psql command via pipe interface.

  gunzip -c mydb.sql.gz | psql -h localhost -U postgres -W -d mydb 
  

5. Split Backup in Multiple Files and Restore

• Backup: PostgreSQL database and split backup in multiple files of specified size. It helps us to backup a large database and transfer to other host easily. As per below example it will split backup files of 100mb in size.

  pg_dump -h localhost -U postgres -W -d mydb | split -b 100m – mydb.pql 
  

• Restore: database backup from multiple splited backup files.

  cat mydb.sql* | psql -h localhost -U postgres -W -d mydb 
  

• Backup: database in compressed splited files of specified size.

  pg_dump -h localhost -U postgres -W -d mydb | gzip | split -b 100m – mydb.sql.gz 
  

• Restore: database from multiple files of compressed files.

  cat mydb.sql.gz* | gunzip | psql -h localhost -U postgres -W -d mydb 
  

Conclusion

In this tutorial, you have learned about backup and restore of PostgreSQL server database, tables.

The post How to Backup and Restore PostgreSQL Database (5 Tips) appeared first on TecAdmin.