In this tutorial, we will show you how to install PostgreSQL on your local system for learning and practicing PostgreSQL.
PostgreSQL was developed for UNIX-like platforms, however, it was designed to be portable. It means that PostgreSQL can also run on other platforms such as Mac OS X, Solaris, and Windows.
Since version 8.0, PostgreSQL offers an installer for Windows systems that makes the installation process easier and faster. For development purpose, we will install PostgreSQL version 11.3 on Linux
There are three steps to complete the PostgreSQL installation:
- Download the PostgreSQL RPM.
- Install the PostgreSQL RPM.
- Verify the postgres installation.
Step 1. Download and install the below rpm’s in postgres server.
|postgresql-infrastructure||Basic Clients and Utilities for PostgreSQL|
|postgresql113||PostgreSQL client programs and libraries|
|postgresql113-contrib||Contributed source and binaries distributed with PostgreSQL|
|postgresql113-devel||PostgreSQL development header files and libraries|
|postgresql113-docs||Extra documentation for PostgreSQL|
|postgresql113-libs||The shared libraries required for any PostgreSQL clients|
|postgresql113-plperl||The Perl procedural language for PostgreSQL|
|postgresql113-plpython||The Python procedural language for PostgreSQL|
|postgresql113-pltcl||The Tcl procedural language for PostgreSQL|
|postgresql113-server||The programs needed to create and run a PostgreSQL server|
|postgresql113-test||The test suite distributed with PostgreSQL|
rpm -ivh postgresql-infrastructure-1-6.noarch.rpm rpm -ivh postgresql113-libs-11.3-1.x86_64.rpm rpm -ivh postgresql113-server-11.3-1.x86_64.rpm rpm -ivh postgresql113-contrib-11.3-1.x86_64.rp rpm -ivh postgresql113-devel-11.3-1.x86_64.rpm rpm -ivh postgresql113-docs-11.3-1.x86_64.rpm rpm -ivh postgresql113-plperl-11.3-1.x86_64.rpm rpm -ivh postgresql113-plpython-11.3-1.x86_64.rpm rpm -ivh postgresql113-pltcl-11.3-1.x86_64.rpm rpm -ivh postgresql113-server-11.3-1.x86_64.rpm rpm -ivh postgresql113-test-11.3-1.x86_64.rpm
Step 2. Now create a linux user to access the postgres database and create 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 nijam # passwd nijam # mkdir -p /data_11.3/ # chown -R nijam. /data_11.3/
Step 3. Initialize the postgres cluster as user nijam.
$ /opt/11.3/bin/initdb -D /data_11.3/ -U nijam
->>> 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.
Step 4. put the Web/Apps details on pg_ha.conf file.
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication nijam trust #host replication nijam 127.0.0.1/32 trust #host replication nijam ::1/128 trust host all all 127.0.0.1/32 trust host all all 18.104.22.168/32 trust host all all 22.214.171.124/32 trust
Step 5 . Allocate the memory and connection as per the CPU and RAM.
For Example if you have 100 GB RAM.
1. Max_connection =1000×16mb(work_mem)=16 GB 2. Effective_cache_size=40gb optimized execution path.the query planner also requires some space 3. Shared_buffer=25GB, 4.Wall_buffer=3% of shared buffer,if lot of insert, update, delete set maximum 8mb 5.Maintenance_work_mem=4GB to 6GB(RAM/8) for vacuum,create,alter, reindex, backup/restoring dump. 6.Temp_buffer=8mb default session-local buffers used only for access to temporary tables.
Including this change the below parameter :
$ cat postgresql.conf listen_addresses = '*' port = 5420 logging_collector=on log_filename = 'postgresql-%a.log' log_directory = 'log' log_rotation_age = 1d archive_mode=on archive_command = 'gzip < %p > /srv/postgresql/var/inst1/log/archive_logs/%f'
Step 6. Startup the new cluster as user nijam .
/opt/11.3/bin/pg_ctl -D /data_11.3/ restart
Step 7. Set the bash profile to access the postgres utility on user nijam home path
$ cat /home/nijam/.profile export PATH=/opt/11.3/bin:$PATH export PGHOME=/opt/11.3/ export PGDATA=/data_11.3/ export LD_LIBRARY_PATH=/opt/11.3/lib export PGDATABASE=nijam export PGUSER=nijam export PGPORT=5420 export PGLOCALEDIR=/opt/11.3/share/locale export MANPATH=$MANPATH:/opt/11.3/share/man
Step 8. Run the bash profile and try to connect the database .
$ ..profile $ psql -p 5420 postgres=# \l --- to list all databases in cluster postgres=# \q --- to quit form postgres console