{"id":7364,"date":"2020-03-04T15:47:28","date_gmt":"2020-03-04T15:47:28","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/postgresql-administration-part-1\/"},"modified":"2026-11-03T19:28:37","modified_gmt":"2026-11-03T19:28:37","slug":"postgresql-administration-part-1","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/postgresql-administration-part-1\/","title":{"rendered":"PostgreSQL administration &#8211; Part 1"},"content":{"rendered":"<p><strong>Introduction<\/strong><\/p>\n<p>PostgreSQL is the world&#8217;s most advanced open-source Relational Database Management&nbsp; System (RDBMS). Many leading organizations, such as Apple, IMDB, Skype, Uber,&nbsp;Lockheed Martin, Verizon, and others, use PostgreSQL. This RDBMS began in 1986&nbsp;as part of the POSTGRES project at the University of California at Berkeley and&nbsp;has more than 30 years of active development on the core platform.<\/p>\n<p>PostgreSQL runs on all major operating systems and has been ACID-compliant since 2001. The acronym ACID consists of the following components:<br \/>\n&nbsp;<\/p>\n<ul>\n<li><strong>Atomicity<\/strong>: You guarantee that either the entire transaction succeeds or&nbsp;none of it does.<\/li>\n<li><strong>Consistency<\/strong>: You guarantee that all data is consistent. All data is valid according to the defined rules. The rules include constraints, cascades, and&nbsp;triggers applied to the database.<\/li>\n<li><strong>Isolation<\/strong>: All transactions occur in isolation. The transaction cannot read data from any other transaction that has not yet been completed.<\/li>\n<li><strong>Durability<\/strong>: After you commit a transaction, it remains in the system, even&nbsp;if there&#8217;s a system crash immediately following the operation<\/li>\n<\/ul>\n<p>Unsurprisingly, PostgreSQL serves as the open-source relational database of&nbsp;choice for many people and organizations because of its powerful add-ons such&nbsp;as the popular PostGIS geospatial database extender.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26401-body-Picture1_19.png\" alt=\"postgres pic 1\" \/><\/p>\n<p class=\"text-align-center\"><a href=\"https:\/\/postgresql-database.blogspot.com\/2013\/08\/postgresql-architecture.html\">Image Source&nbsp;<\/a><\/p>\n<p><strong>Support<\/strong><\/p>\n<p>Production support SLA&#8217;s available from the following companies:<\/p>\n<p>&#8211; <a href=\"https:\/\/www.enterprisedb.com\">https:\/\/www.enterprisedb.com<\/a><br \/>\n&#8211; <a href=\"https:\/\/www.2ndquadrant.com\/\">https:\/\/www.2ndquadrant.com<\/a><br \/>\n&#8211; <a href=\"https:\/\/www.revsys.com\/\">https:\/\/www.revsys.com<\/a>\/<br \/>\n&#8211; <a href=\"https:\/\/imperoit.com\/PostgreSQL_Support.htm\">https:\/\/imperoit.com\/PostgreSQL_Support.htm<\/a><\/p>\n<p><strong>Supported Versions:<\/strong> Current (12) \/ 11 \/ 10 \/ 9.6 \/ 9.5 \/ 9.4<br \/>\n<strong>Development Versions:<\/strong> development<br \/>\n<strong>Unsupported versions:<\/strong> 9.3 \/ 9.2 \/ 9.1 \/ 9.0 \/ 8.4 \/ 8.3 \/ 8.2<\/p>\n<p><strong>Installation and Configuration<\/strong><\/p>\n<p><strong>Perform the following steps to install and configure Postgres 9.3:<\/strong><\/p>\n<p><strong>Install Postgres 9.3 on Linux 7.1<\/strong><\/p>\n<p>Run the following command to install Postgres 9.3 on Red Hat&amp;reg; Linux 7.1:<\/p>\n<p>\n<em>&nbsp; &nbsp; [root@snwdbsolpeprod01 ~]# cat \/etc\/redhat-release<br \/>\n&nbsp; &nbsp; Red Hat Enterprise Linux Server release 7.1 (Maipo)<\/em><\/p>\n<p><strong>Create an empty folder<\/strong><br \/>\nCreate an empty folder for the database installation.<\/p>\n<p><em>&nbsp; &nbsp; [root@snwdbsolpeprod01 mnt]# mkdir postt<br \/>\n&nbsp; &nbsp; [root@snwdbsolpeprod01 postt]# pwd<br \/>\n&nbsp; &nbsp; \/mnt\/postt<\/em><\/p>\n<p><strong>Download the RPM<\/strong><\/p>\n<p>Run the following command to download the Red Hat Package Manager (RPM) for your<br \/>\nOS version to start the Postgres installation:<\/p>\n<p><em>&nbsp;[root@snwdbsolpeprod01 postt]# wget https:\/\/yum.postgresql.org\/9.3\/redhat\/rhel-7-x86_64\/pgdg-redhat93-9.3-2.noarch.rpm<\/em><\/p>\n<p><strong>Install the RPM<\/strong><\/p>\n<p>Install the RPM package by using the following command:<\/p>\n<p><em>root@snwdbsolpeprod01 postt]# rpm -ivh pgdg-redhat93-9.3-2.noarch.rpm<\/em><\/p>\n<p>&nbsp;<\/p>\n<p><strong>Install additional packages<\/strong><\/p>\n<p>After you install RPM, you need to install some Postgre packages that install<br \/>\nthe DB software.<\/p>\n<p><em>&nbsp; &nbsp; [root@snwdbsolpeprod01 postt]# yum install postgresql-contrib.x86_64<br \/>\n&nbsp; &nbsp; [root@snwdbsolpeprod01 postt]# yum install postgresql93-server.x86_64<\/em><\/p>\n<p>&nbsp;<\/p>\n<p><strong>Configure the PGDATA location<\/strong><\/p>\n<p>Determine where to store the data. To use a non-default location for data, edit<br \/>\nthe PostgreSQL service <strong>sysconfig<\/strong>&nbsp;file, and change the PGDATA argument.<\/p>\n<p><em>&nbsp; &nbsp; vi \/etc\/rc.d\/init.d\/postgresql<br \/>\n&nbsp; &nbsp; vi \/etc\/sysconfig\/pgsql\/postgresql<\/em><\/p>\n<p><strong>NOTE<\/strong><em>:<\/em> If PostgreSQL in <strong>sysconfig\/pgsql<\/strong>&nbsp;does not exist, create it and&nbsp;add a line denoting where you want to store the data, as shown in the following&nbsp;example:<\/p>\n<p>\n<em>&nbsp; &nbsp; [root@snwdbsolpeprod01 pgsql]# cd \/etc\/sysconfig\/pgsql\/<br \/>\n&nbsp; &nbsp; [root@snwdbsolpeprod01 pgsql]# vi postgresql<br \/>\n&nbsp; &nbsp; [root@snwdbsolpeprod01 pgsql]# cat postgresql<br \/>\n&nbsp; &nbsp; PDGATA=\/mnt\/postt<\/em><\/p>\n<p><strong>Initialize the database<\/strong><\/p>\n<p>\nThe first command (only needed once) is to initialize the database in PGDATA.<\/p>\n<p><em>&nbsp; &nbsp; service &lt;name&gt; initdb<\/em><\/p>\n<p>For example: for version 9.3:<\/p>\n<p><em>&nbsp; &nbsp; service postgresql-9.3 initdb<\/em><\/p>\n<p>or<\/p>\n<p><em>&nbsp; &nbsp; \/usr\/pgsql-9.3\/bin\/postgresql93-setup initdb<\/em><\/p>\n<p><em>&nbsp; &nbsp; [root@snwdbsolpeprod01 data]# \/usr\/pgsql-9.3\/bin\/postgresql93-setup initdb<br \/>\n&nbsp; &nbsp; Initializing database &#8230; OK<\/em><\/p>\n<p><strong>Set Postgres to start automatically<\/strong><\/p>\n<p>If you want PostgreSQL to start automatically when the OS starts, use the&nbsp;following command:<\/p>\n<p><em>&nbsp;[root@snwdbsolpeprod01 data]# chkconfig postgresql-9.3 on<\/em><\/p>\n<p><strong>Note<\/strong>: Forwarding request to &#8216;systemctl enable postgresql-9.3.service&#8217;.<\/p>\n<p><strong>Start the PostgreSQL service<\/strong><\/p>\n<p>To start the PostgreSQL service, run the following command:<\/p>\n<p><em>[root@snwdbsolpeprod01 data]# systemctl start postgresql-9.3.service<\/em><\/p>\n<p><strong>Configure the database<\/strong><\/p>\n<p>You can easily configure the database by updating postgresql.conf as shown in<br \/>\nthe following example:<\/p>\n<p>&nbsp; &nbsp; vi \/var\/lib\/pgsql\/9.3\/data\/postgresql.conf<\/p>\n<p>Change the following:<\/p>\n<p>&nbsp; &nbsp; listen_address = \u2018*\u2019<br \/>\n&nbsp; &nbsp; port = 15000<br \/>\n&nbsp; &nbsp; max_connections=300<br \/>\n&nbsp; &nbsp; shared_buffers = 8192MB &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # min 128kB<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # (change requires restart)<br \/>\n&nbsp; &nbsp; temp_buffers = 128MB &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;# min 800kB<br \/>\n&nbsp; &nbsp; max_prepared_transactions = 20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;# zero disables the feature<\/p>\n<p>&nbsp; &nbsp; log_destination = &#8216;csvlog&#8217;<br \/>\n&nbsp; &nbsp; logging_collector = on<br \/>\n&nbsp; &nbsp; log_directory = &#8216;\/mnt\/pgsql\/logs&#8217;<br \/>\n&nbsp; &nbsp; log_filename = &#8216;postgresql-%a.log&#8217;<\/p>\n<p>&nbsp; &nbsp; #&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<br \/>\n&nbsp; &nbsp; # AUTOVACUUM PARAMETERS<br \/>\n&nbsp; &nbsp; #&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<\/p>\n<p>&nbsp; &nbsp; autovacuum = on<br \/>\n&nbsp; &nbsp; # Enable autovacuum subprocess? &#8216;on&#8217;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # requires track_counts to also be on.<br \/>\n&nbsp; &nbsp; #log_autovacuum_min_duration = -1 &nbsp; &nbsp; &nbsp; # -1 disables, 0 logs all actions and<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # their durations, &gt; 0 logs only<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # actions running at least this number<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # of milliseconds.<br \/>\n&nbsp; &nbsp; autovacuum_max_workers = 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;# max number of autovacuum subprocesses<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # (change requires restart)<br \/>\n&nbsp; &nbsp; autovacuum_naptime = 10080min &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # time between autovacuum runs<br \/>\n&nbsp; &nbsp; autovacuum_vacuum_threshold = 1000 &nbsp; &nbsp; &nbsp;# min number of row updates before<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # vacuum<br \/>\n&nbsp; &nbsp; #autovacuum_analyze_threshold = 50 &nbsp; &nbsp; &nbsp;# min number of row updates before<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # analyze<br \/>\n&nbsp; &nbsp; #autovacuum_vacuum_scale_factor = 0.2 &nbsp; # fraction of table size before vacuum<br \/>\n&nbsp; &nbsp; #autovacuum_analyze_scale_factor = 0.1 &nbsp;# fraction of table size before analyze<br \/>\n&nbsp; &nbsp; #autovacuum_freeze_max_age = 200000000 &nbsp;# maximum XID age before forced vacuum<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # (change requires restart)<br \/>\n&nbsp; &nbsp; #autovacuum_multixact_freeze_max_age = 400000000 &nbsp; &nbsp; &nbsp; &nbsp;# maximum Multixact age<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # before forced vacuum<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # (change requires restart)<br \/>\n&nbsp; &nbsp; #autovacuum_vacuum_cost_delay = 20ms &nbsp; &nbsp;# default vacuum cost delay for<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # autovacuum, in milliseconds;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # -1 means use vacuum_cost_delay<br \/>\n&nbsp; &nbsp; #autovacuum_vacuum_cost_limit = -1 &nbsp; &nbsp; &nbsp;# default vacuum cost limit for<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # autovacuum, -1 means use<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; # vacuum_cost_limit<\/p>\n<p><strong>Configure database connection settings<\/strong><\/p>\n<p>To restrict or manage the database connection setting, run the following command:<\/p>\n<p>&nbsp; &nbsp; vi \/var\/lib\/pgsql\/9.3\/data\/pg_hba.conf<\/p>\n<p>&nbsp; &nbsp; # TYPE &nbsp;DATABASE &nbsp; &nbsp; &nbsp; &nbsp;USER &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ADDRESS &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; METHOD<\/p>\n<p>&nbsp; &nbsp; # &#8220;local&#8221; is for Unix domain socket connections only<br \/>\n&nbsp; &nbsp; local &nbsp; all &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; all &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; peer<br \/>\n&nbsp; &nbsp; local &nbsp; all &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; postgres &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;md5<br \/>\n&nbsp; &nbsp; local &nbsp; all &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; postgres &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ident<br \/>\n&nbsp; &nbsp; # IPv4 local connections:<br \/>\n&nbsp; &nbsp; # IPv6 local connections:<br \/>\n&nbsp; &nbsp; host &nbsp; &nbsp;all &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; all &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ::1\/128 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ident<br \/>\n&nbsp; &nbsp; host &nbsp; &nbsp;all &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; all &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.0.0.0\/0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; md5<br \/>\n&nbsp; &nbsp; # Allow replication connections from localhost, by a user with the<br \/>\n&nbsp; &nbsp; # replication privilege.<br \/>\n&nbsp; &nbsp; #local &nbsp; replication &nbsp; &nbsp; postgres &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;peer<br \/>\n&nbsp; &nbsp; #host &nbsp; &nbsp;replication &nbsp; &nbsp; postgres &nbsp; &nbsp; &nbsp; &nbsp;127.0.0.1\/32 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ident<br \/>\n&nbsp; &nbsp; #host &nbsp; &nbsp;replication &nbsp; &nbsp; postgres &nbsp; &nbsp; &nbsp; &nbsp;::1\/128 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ident<br \/>\n&nbsp;<\/p>\n<p><strong>Configure the firewall<\/strong><\/p>\n<p>To configure the port on the firewall, run the following commands:<\/p>\n<p><em>&nbsp;iptables -I INPUT -p tcp &#8211;dport 15000 &#8211;syn -j ACCEPT<\/em><\/p>\n<p><em>&nbsp; &nbsp; service iptables save<\/em><\/p>\n<p><em>&nbsp; &nbsp; service iptables restart<\/em><\/p>\n<p><em>&nbsp; &nbsp; root@snwdbsolpeprod01 postt]# service postgresql-9.3 restart<\/em><\/p>\n<p><strong>Create a user or role<\/strong><\/p>\n<p>To create a new user or role in the database, run the following commands:<\/p>\n<p><em>&nbsp; su \u2013 postgres<\/em><\/p>\n<p><em>&nbsp; &nbsp; psql -p 15000<\/em><\/p>\n<p><em>&nbsp; &nbsp; postgres=# CREATE ROLE OCT1 LOGIN<br \/>\n&nbsp; &nbsp; &nbsp; UNENCRYPTED PASSWORD &#8216;test@123&#8217;<br \/>\n&nbsp; &nbsp; &nbsp; INHERIT REPLICATION;<\/em><br \/>\n&nbsp;<\/p>\n<p><strong>Create a tablespace<\/strong><\/p>\n<p>To create a new tablespace on the database, run the following command:<\/p>\n<p><em>&nbsp; &nbsp;postgres=# CREATE TABLESPACE OCT1_tablespace<br \/>\n&nbsp; &nbsp; &nbsp; OWNER ilusr<br \/>\n&nbsp; &nbsp; &nbsp; LOCATION &#8216;\/usrdata\/pgsql\/data\/oct&#8217;;<\/em><\/p>\n<p><strong>Create a database<\/strong><\/p>\n<p>To create a new database, run the following command:<\/p>\n<p><em>&nbsp; &nbsp;postgres=# CREATE DATABASE OCT1<br \/>\n&nbsp; &nbsp; &nbsp; WITH ENCODING=&#8217;UTF8&#8242;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;OWNER=test<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;LC_CTYPE=&#8217;en_US.UTF-8&#8242;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;CONNECTION LIMIT=-1<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;TABLESPACE=OCT1_tablespace;<\/em><\/p>\n<p><strong>Basic commands<\/strong><\/p>\n<p>Some basic administration commands include the following ones<\/p>\n<p><strong>Stop and start PostgreSQL<\/strong><\/p>\n<p>&nbsp; &nbsp; \/opt\/PostgreSQL\/9.3\/bin\/pg_ctl -D \/mnt\/postt\/data stop<br \/>\n&nbsp; &nbsp; \/opt\/PostgreSQL\/9.3\/bin\/pg_ctl -D \/mnt\/postt\/data start<br \/>\n&nbsp; &nbsp; \/opt\/PostgreSQL\/9.3\/bin\/pg_ctl -D \/mnt\/postt\/data restart<br \/>\n&nbsp; &nbsp; \/opt\/PostgreSQL\/9.3\/bin\/pg_ctl -D opt\/PostgreSQL\/9.4\/data \u2013m smart stop #wait for complete the transactions<br \/>\n&nbsp; &nbsp; \/opt\/PostgreSQL\/9.3\/bin\/pg_ctl -D \/mnt\/postt\/data \u2013m fast stop #Immediate stop<br \/>\n&nbsp; &nbsp; \/opt\/PostgreSQL\/9.3\/bin\/pg_ctl -D \/mnt\/postt\/data \u2013m immediate stop #Abort the DB<br \/>\n&nbsp; &nbsp; \/opt\/PostgreSQL\/9.3\/bin\/pg_ctl -D \/mnt\/postt\/data \u2013m smart restart<br \/>\n&nbsp; &nbsp; \/opt\/PostgreSQL\/9.3\/bin\/pg_ctl -D \/mnt\/postt\/data \u2013m fast restart<br \/>\n&nbsp; &nbsp; \/opt\/PostgreSQL\/9.3\/bin\/pg_ctl -D \/mnt\/postt\/data \u2013m immediate restart<\/p>\n<p><strong>Check the PostgreSQL version<\/strong><\/p>\n<p><em>&nbsp; postgres=# select version();<\/em><\/p>\n<p><strong>Identify the activity on a specific database level<\/strong><\/p>\n<p><em>select pid,backend_xid,backend_xmin,query from pg_stat_activity ;<\/em><\/p>\n<p><strong>Analyze table status<\/strong><\/p>\n<p><em>select relname,last_autoanalyze,last_analyze,n_mod_since_analyze from pg_stat_all_tables;<\/em><\/p>\n<p><strong>Find the physical path of a table<\/strong><\/p>\n<p><em>&nbsp;postgres=# SELECT pg_relation_filepath(&#8216;testpitr1&#8217;);<\/em><\/p>\n<p><em>&nbsp; &nbsp; &nbsp;pg_relation_filepath<br \/>\n&nbsp; &nbsp; &nbsp;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n&nbsp; &nbsp; &nbsp;base\/13003\/16399<\/em><\/p>\n<p><em>&nbsp; &nbsp; [postgres@postgres221 data]$ ls -l \/mnt\/postt\/data\/base\/13003\/16399<br \/>\n&nbsp; &nbsp; -rw&#8212;&#8212;- 1 postgres postgres 256024576 Feb 21 06:36 \/mnt\/postt\/data\/base\/13003\/16399<\/em><\/p>\n<p><strong>Get schema names inside an instance or cluster<\/strong><\/p>\n<p><em>&nbsp;select schema_name from information_schema.schemata;<\/em><\/p>\n<p><em>&nbsp; &nbsp; select nspname from pg_catalog.pg_namespace;<\/em><\/p>\n<p><em>&nbsp; &nbsp; post_gre=# \\dn<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;List of schemas<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;Name &nbsp; &nbsp; &nbsp; | &nbsp;Owner<br \/>\n&nbsp; &nbsp; &#8212;&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;-<br \/>\n&nbsp; &nbsp; &nbsp;kailash_test | postgres<br \/>\n&nbsp; &nbsp; &nbsp;public &nbsp; &nbsp; &nbsp; | postgres<br \/>\n&nbsp; &nbsp; (2 rows)<\/em><\/p>\n<p><strong>Get dbnames inside an instance or cluster<\/strong><\/p>\n<p>&nbsp;template1=# select datname from pg_database;<br \/>\n&nbsp; &nbsp; &nbsp;template1<br \/>\n&nbsp; &nbsp; &nbsp;template0<br \/>\n&nbsp; &nbsp; &nbsp;post_gre<\/p>\n<p>&nbsp; &nbsp; &nbsp;template1=# \\l<br \/>\n&nbsp; &nbsp; &nbsp;post_gre &nbsp;| postgres | UTF8 &nbsp; &nbsp; | en_US.UTF-8 | en_US.UTF-8 | =Tc\/postgres &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;+<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | postgres=CTc\/postgres +<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | kailash_s=CTc\/postgres<br \/>\n&nbsp; &nbsp; &nbsp;template0 | postgres | UTF8 &nbsp; &nbsp; | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | postgres=CTc\/postgres<br \/>\n&nbsp; &nbsp; &nbsp;template1 | postgres | UTF8 &nbsp; &nbsp; | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |<\/p>\n<p>&nbsp; &nbsp; template1=# select usename from pg_catalog.pg_user;<br \/>\n&nbsp; &nbsp; &nbsp;kailash<br \/>\n&nbsp; &nbsp; &nbsp;kailash_s<br \/>\n&nbsp; &nbsp; &nbsp;postgres<\/p>\n<p>&nbsp; &nbsp; template1=# \\du<br \/>\n&nbsp; &nbsp; &nbsp;kailash &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| {}<br \/>\n&nbsp; &nbsp; &nbsp;kailash_s | Superuser, Create role, Create DB &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| {}<br \/>\n&nbsp; &nbsp; &nbsp;postgres &nbsp;| Superuser, Create role, Create DB, Replication | {}<\/p>\n<p><strong>Conclusion<\/strong><\/p>\n<p>PostgreSQL&#8217;s tag line claims that it is &#8220;The world&#8217;s most advanced open source database.&#8221; However, PostgreSQL isn&#8217;t just relational, it&#8217;s object relational.This distinction gives it some advantage over other open source SQL databases such as MySQL, MariaDB, and Firebird. PostgreSQL is an obvious choice on AWS cloud for migrating in-house RDBMS to an Open Source RDBMS on CLOUD.<br \/>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post introduces PostgreSQL and walks you through the installation and configuration of version 9.3 on Linux\u00ae<\/p>\n","protected":false},"author":2,"featured_media":7366,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26401","content_type":"blog","created_by":"10130","authored_on":"2023-12-19T15:47:27+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Kailash Chandra Kalkhundiya Oracle Database Administrator, Rackspace Technology","seo_description":"Start PostgreSQL administration on Linux by installing and configuring version 9.3, unlocking the advanced capabilities of this open-source RDBMS.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26401-insights_image-database-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"PostgreSQL","product":"","publisheddate":"2020-03-04T15:47:28","rxt_solutions":"Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"PostgreSQL Administration  Installation and Configuration | Rackspace","field_topics":"","field_slug":"postgresql-administration-part-1","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7364","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7364","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7364"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7364\/revisions"}],"predecessor-version":[{"id":9417,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7364\/revisions\/9417"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7366"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7364"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}