PostgreSQL

How to Install PostgreSQL 10 on CentOS/RHEL and Fedora

How to Install PostgreSQL 10 on CentOS/RHEL and Fedora &-8211; this Article or News was published on this date:2019-05-28 16:46:33 kindly share it with friends if you find it helpful

PostgreSQL is a powerful, highly scalable, open source and cross-platform object-relational database system that runs on Unix-like operating systems including Linux and Windows OS. It is an enterprise level database system which is highly reliable and offers data integrity and correctness to users.

In this article, we will explain how to install latest version of PostgreSQL 10 on CentOS, RHEL, Oracle Enterprise Linux, Scientific Linux and Fedora using official PostgreSQL Yum repository.

Add PostgreSQL Yum Repository

This official PostgreSQL Yum repository will combine with your Linux system and offers automatic updates for all supported versions of PostgreSQL on RedHat based distributions such as CentOS, Scientific Linux and Scientific Linux, as well as current versions of Fedora.

Note that due to the shorter Fedora support cycle, not all versions are available and we suggest that do not use Fedora for server deployments.

To use the yum repository, follow these steps:

--------------- On RHEL/CentOS 7 and Scientific Linux/Oracle Linux 7 --------------- 
- yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat10-10-1.noarch.rpm

--------------- On 64-Bit RHEL/CentOS 6 and Scientific Linux/Oracle Linux 6 --------------- 
- yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-6-x86_64/pgdg-redhat10-10-1.noarch.rpm

--------------- On 32-Bit RHEL/CentOS 6 and Scientific Linux/Oracle Linux 6 --------------- 
- yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-6-i386/pgdg-redhat10-10-1.noarch.rpm

--------------- On Fedora 26 --------------- 
- dnf install https://download.postgresql.org/pub/repos/yum/10/fedora/fedora-26-x86_64/pgdg-fedora10-10-2.noarch.rpm

--------------- On Fedora 25 --------------- 
- dnf install https://download.postgresql.org/pub/repos/yum/10/fedora/fedora-25-x86_64/pgdg-fedora10-10-3.noarch.rpm

--------------- On Fedora 24 --------------- 
- dnf install https://download.postgresql.org/pub/repos/yum/10/fedora/fedora-24-x86_64/pgdg-fedora10-10-3.noarch.rpm
Enable PostgreSQL Yum RepositoryEnable PostgreSQL Yum Repository

Enable PostgreSQL Yum Repository

Install PostgreSQL Server

After adding PostgreSQL yum repository in your respective Linux distribution, use the following command to install PostgreSQL server and client packages.

- yum install postgresql10-server postgresql10   [On RedHat based Distributions]
- dnf install postgresql10-server postgresql10   [On Fedora Linux]
Enable PostgreSQL Yum RepositoryInstall PostgreSQL Server

Install PostgreSQL Server

Important: PostgreSQL data directory /var/lib/pgsql/10/data/ contains all of the data files for the database.

Initialize PostgreSQL Database

Due to some policies for Red Hat based distributions, the PostgreSQL installation will not be active for automatic start or have the database initialized automatically. To complete your database installation, you need to initialize your database before using it for first time.

- /usr/pgsql-10/bin/postgresql-10-setup initdb
Enable PostgreSQL Yum RepositoryInitialize PostgreSQL Database

Initialize PostgreSQL Database

Start and Enable PostgreSQL Server

After database initialize completes, start PostgreSQL service and enable PostgreSQL service to auto start on system boot.

--------------- On SystemD --------------- 
- systemctl start postgresql-10
- systemctl enable postgresql-10
- systemctl status postgresql-10 

--------------- On SysVinit --------------- 
- service postgresql-10 start
- chkconfig postgresql-10 on
- service postgresql-10 status
Enable PostgreSQL Yum RepositoryStart PostgreSQL Server

Start PostgreSQL Server

Verify PostgreSQL Installation

After installing PostgreSQL 10 on your server, verify its installation by connecting to postgres database server.

- su - postgres
$ psql

psql (10.0)
Type "help" for help.

If you want you can create a password for user postgres for security purpose.

postgres=- password postgres
Enable PostgreSQL Yum RepositorySet PostgreSQL User Password

Set PostgreSQL User Password

You can find more information at the PostgreSQL Homepage: https://www.postgresql.org/

Also check out these articles about popular database management systems:

  1. How to Install and Secure MariaDB 10 in CentOS 7
  2. How to Install and Secure MariaDB 10 in CentOS 6
  3. Install MongoDB Community Edition 3.2 on Linux Systems

That’s all! Hope you find this article useful. If you have any questions or thoughts to share, use the comment section below.

How to Install PostgreSQL 9.6 on Debian and Ubuntu

How to Install PostgreSQL 9.6 on Debian and Ubuntu &-8211; this Article or News was published on this date:2019-05-28 16:45:41 kindly share it with friends if you find it helpful

PostgreSQL is a powerful, highly scalable, open source and cross-platform object-relational database system that runs on Unix-like operating systems including Linux and Windows OS. It is an enterprise level database system which is highly reliable and offers data integrity and correctness to users.

In our earlier article, we’ve explained PostgreSQL 10 installation on CentOS/RHEL and Fedora. In this article, we will explain how to install PostgreSQL 9.6 on Debian, Ubuntu and its derivatives using official PostgreSQL APT repository.

Add PostgreSQL APT Repository

This official PostgreSQL APT repository will combine with your Linux system and offers automatic updates for all supported versions of PostgreSQL on Debian and Ubuntu distributions.

To add the apt repository, first create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository as per your distribution.

On Ubuntu Systems

--------------- On Ubuntu 17.04 ---------------
deb http://apt.postgresql.org/pub/repos/apt/ zesty-pgdg main

--------------- On Ubuntu 16.04 ---------------
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main

--------------- On Ubuntu 14.04 ---------------
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

On Debian Systems

--------------- On Stretch 9.x ---------------
deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main

--------------- On Jessie 8.x ---------------
deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main

--------------- On Wheezy 7.x ---------------
deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main

Then import the repository signing key, and update the system package lists like this.

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt update 
Add PostgreSQL KeyAdd PostgreSQL Key

Add PostgreSQL Key

Install PostgreSQL Server

Once you have added the PostgreSQL apt repository in your respective Linux distribution, now install PostgreSQL server and client packages as follows:

$ sudo apt install postgresql-9.6-server postgresql-9.6  
Add PostgreSQL KeyInstall Postgresql Database Server

Install Postgresql Database Server

Important: Unlike in RHEL/CentOS/Fedora where you have to manually initialize the database system, in Ubuntu/Debian, it is initialized automatically. Therefore simply proceed to start database server as described in the next section.

The PostgreSQL data directory /var/lib/postgresql/9.6/main contains all of the data files for the database.

Start and Enable PostgreSQL Server

With the database server initialized, start PostgreSQL service and enable PostgreSQL service to auto start on system boot like this.

--------------- On SystemD --------------- 
$ sudo systemctl start postgresql.service
$ sudo systemctl enable postgresql.service 
$ sudo systemctl status postgresql.service 

--------------- On SysVinit --------------- 
$ sudo service postgresql-9.6 start
$ sudo chkconfig postgresql on
$ sudo service postgresql-9.6 status
Add PostgreSQL KeyStart and Enable PostgreSQL Database

Start and Enable PostgreSQL Database

Verify PostgreSQL Installation

After installing PostgreSQL database system on your server, verify its installation by connecting to postgres database server. The PostgreSQL administrator user is named as postgres, type this command to access the user system account.

$ sudo su postgres
- cd
- psql
Add PostgreSQL KeyVerify PostgreSQL Database

Verify PostgreSQL Database

To set a password for the postgre database administrator user, use this command:

postgres=- password postgres
Add PostgreSQL KeySet Password for PostgreSQL Admin

Set Password for PostgreSQL Admin

To secure the postgre user system account, use the password command below.

$ sudo passwd postgres 

Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

$su - postgre
$ ls
$ psql
Add PostgreSQL KeySet Password for Postgre User

Set Password for Postgre User

For more information, go to the PostgreSQL Homepage: https://www.postgresql.org/

Lastly, also read through these articles about popular database management systems:

  1. Installing MariaDB 10.1 in Debian Jessie and Running Various MariaDB Queries
  2. How to Change a Default MySQL/MariaDB Data Directory in Linux
  3. How to Install and Secure MariaDB 10 in CentOS 7
  4. How to Install and Secure MariaDB 10 in CentOS 6
  5. Install MongoDB Community Edition 3.2 on Linux Systems

That’s all for now! To share any thoughts with us, make use of the feedback form below. Remember to always stay connected to sfnews.com for interesting Linux stuff.

How to Install PostgreSQL 10 Using Source Code in Linux

How to Install PostgreSQL 10 Using Source Code in Linux &-8211; this Article or News was published on this date:2019-05-28 16:39:49 kindly share it with friends if you find it helpful

PostgreSQL also called Postgres is a powerful and open source object-relational database system. It is an enterprise level database having features such as write ahead logging for fault tolerance, asynchronous replication, Multi-Version Concurrency Control (MVCC),online/hot backups, point in time recovery, query planner/optimizer, tablespaces, nested transactions (savepoints) etc.

Postgres has its latest version 10 released on 5th Oct 2017 by postgres global development group.

PostgreSQL Features

Features of New version are as follows:

  • Logical Replication: This feature enables replication of individual database objects (be it rows, tables, or selective databases) across standby servers. It provides more control over data replication. Implemented by using publisher-subscriber model.
  • Quorum Commit for Synchronous Replication: In this feature, dba can now specify the number of standby’s that acknowledge that the changes to database has done, so that data can be considered safely written.
  • SCRAM-SHA-256 authentication: Improved security that existing MD5-based password authentication and storage.
  • Improved parallel query execution.
  • Declarative table partitioning.
  • Full text search support for JSON and JSONB.

In this article, we will explain how to install PostgreSQL 10 using source code installation in Linux systems. Those who looking for easy installation from distribution package manager they can follow these below guides.

  1. How to Install PostgreSQL 10 on CentOS/RHEL and Fedora
  2. How to Install PostgreSQL 10 on Debian and Ubuntu

Install PostgreSQL Using Source Code

As postgres is open source database, it can be built from source code according to one’s needs/requirements. we can customize the build and installation process by supplying one or more command line options for various additional features.

Major advantage of using source code installation is it can be highly customized during installation.

1. First install required prerequisites such as gcc, readline-devel and zlib-devel using package manager as shown.

- yum install gcc zlib-devel readline-devel     [On RHEL/CentOS]
- apt install gcc zlib1g-dev libreadline6-dev   [On Debian/Ubuntu]

2. Download the source code tar file from the official postgres website using the following wget command directly on system.

- wget https://ftp.postgresql.org/pub/source/v10.0/postgresql-10.0.tar.bz2

3. Use tar command to extract the downloaded tarball file. New directory named postgresql-10.0 will be created.

- tar -xvf postgresql-10.0.tar.bz2
- ll
Sample Output
total 19236
-rw-------. 1 root root      933 Mar 18  2015 anaconda-ks.cfg
-rw-r--r--. 1 root root     8823 Mar 18  2015 install.log
-rw-r--r--. 1 root root     3384 Mar 18  2015 install.log.syslog
drwxrwxrwx  6 1107 1107     4096 Oct  3  2017 postgresql-10.0
-rw-r--r--  1 root root 19639147 Oct  3  2017 postgresql-10.0.tar.bz2

4. Next step for installation procedure is to configure the downloaded source code by choosing the options according to your needs.

- cd postgresql-10.0

use ./configure --help to get help about various options.

Sample Output
- ./configure --help

Defaults for the options are specified in brackets.
Configuration:
  -h, --help              display this help and exit
      --help=short        display options specific to this package
      --help=recursive    display the short help of all the included packages
  -V, --version           display version information and exit
  -q, --quiet, --silent   do not print `checking ...' messages
      --cache-file=FILE   cache test results in FILE [disabled]
  -C, --config-cache      alias for `--cache-file=config.cache'
  -n, --no-create         do not create output files
      --srcdir=DIR        find the sources in DIR [configure dir or `..']

Installation directories:
  --prefix=PREFIX         install architecture-independent files in PREFIX
                          [/usr/local/pgsql]
  --exec-prefix=EPREFIX   install architecture-dependent files in EPREFIX
                          [PREFIX]

5. Now create a directory where you want to install postgres files and use prefix option with configure.

- mkdir /opt/PostgreSQL-10/
- ./configure --prefix=/opt/PostgreSQL-10
Sample Output
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for WAL segment size... 16MB
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables... 
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking whether gcc supports -Wdeclaration-after-statement... yes
checking whether gcc supports -Wendif-labels... yes
checking whether gcc supports -Wmissing-format-attribute... yes
checking whether gcc supports -Wformat-security... yes
checking whether gcc supports -fno-strict-aliasing... yes
checking whether gcc supports -fwrapv... yes
checking whether gcc supports -fexcess-precision=standard... no
....

6. After configuring, next we will start to build postgreSQL using following make command.

- make

After build process finishes, now install postgresql using following command.

- make install

Postgresql 10 has been installed in /opt/PostgreSQL-10 directory.

7. Now create a postgres user and directory to be used as data directory for initializing database cluster. Owner of this data directory should be postgres user and permissions should be 700 and also set path for postgresql binaries for our ease.

- useradd postgres
- passwd postgres
- mkdir /pgdatabase/data
- chown -R postgres. /pgdatabase/data
- echo 'export PATH=$PATH:/opt/PostgreSQL-10/bin' > /etc/profile.d/postgres.sh

8. Now initialize database using the following command as postgres user before using any postgres commands.

- su postgres
$ initdb -D /pgdatabase/data/ -U postgres -W

Where -D is location for this database cluster or we can say it is data directory where we want to initialize database cluster, -U for database superuser name and -W for password prompt for db superuser.

For more info and options we can refer initdb –help.

9. After initializing database, start the database cluster or if you need to change port or listen address for server, edit the postgresql.conf file in data directory of database server.

Configure PostgreSQL PortConfigure PostgreSQL Port

Configure PostgreSQL Port

$ pg_ctl -D /pgdatabase/data/ -l /pglog/db_logs/start.log start

10. After starting database, verify the status of postgres server process by using following commands.

$ ps -ef |grep -i postgres
$ netstat -apn |grep -i 51751
Configure PostgreSQL PortVerify PostgreSQL Database

Verify PostgreSQL Database

We can see that database cluster is running fine, and startup logs can be found at location specified with -l option while starting database cluster.

11. Now connect to database cluster and create database by using following commands.

$ psql -p 51751
postgres=- create database test;
postgres=- l to list all databases in cluster
postgres=- q to quit form postgres console
Configure PostgreSQL PortConnect PostgreSQL Database

Connect PostgreSQL Database

That’s It! in our upcoming articles, I will cover configuration, replication setup and installation of pgAdmin tool, till then stay tuned to sfnews.