Database Insider

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;">In this post, I am sharing few important function for finding the size of database, table and index in PostgreSQL.<br>Finding object size in postgresql database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in GB. The scripts have been formatted to work very easily with PUTTY SQL Editor. <br><br><b>1. Checking table size excluding table dependency:</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT pg_size_pretty(pg_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty---------------- 238 MB(1 row)</pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>2. Checking table size including table dependency:</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT pg_size_pretty(pg_total_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty---------------- 268 MB(1 row)</pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>3. Finding individual postgresql database size</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT pg_size_pretty(pg_database_size('db_name'));</pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>4. Finding individual table size for postgresql database -including dependency index:</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT pg_size_pretty(pg_total_relation_size('Employee_Table'));<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>5. Individual table size for postgresql database - not including dependency size:</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT pg_size_pretty(pg_relation_size('Employee_table'));</pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>6. Individual index size for postgresql database:</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT pg_size_pretty(pg_indexes_size('index_empid'));<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>7. Finding postgresql tablespace size</b></span></span><br><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;">The following statement returns the size of the tablespace</span></span><b> </b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT pg_size_pretty (pg_tablespace_size ('tablespace_name'));<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>8. Get a listing of all of your databases in Postgres and their sizes in GB, ordering by the largest size first</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024/1024 AS size_in_GB FROM pg_database ORDER by size_in_GB DESC; database_name | size_in_gb---------------+------------ mumbai|422 template1 |  0 template0 |  0(3 rows)<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>Get a listing of all of your databases in Postgres and their sizes by using meta-command </b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">nellore=# \l+<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>9. Script to Find all the table size in the current database.</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECTtable_schema || '.' || table_name AS TableName,pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSizeFROM information_schema.tablesORDER BYpg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>10. Script to Find all the table and index size in the current database.</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECTTableName,pg_size_pretty(pg_table_size(TableName)) AS TableSize,pg_size_pretty(pg_indexes_size(TableName)) AS IndexSize,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSizeFROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName FROM information_schema.tables) AS TablesORDER BY 4 DESC<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>11. Checking table size as well as dependencies size</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT  schemaname, relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As " table_Size", pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;  schemaname | Table  | table_Size | External Size-----------------------+----------------------------------------+------------+--------------- mhrordhu_dhu  | ror_bulk_sign_data_audit | 7940 MB| 6632 MB mhrordhu_shi  | ror_bulk_sign_data_audit | 7288 MB| 6104 MB mhrordhu_shi  | ror_sign_tables_audit  | 3458 MB| 61 MB mhrordhu_sak  | ror_bulk_sign_data_audit | 3174 MB| 2667 MB mhrordhu_dhu_os | holder_detail_audit| 2794 MB| 776 kB mhrordhu_sak  | tbl_summary_audit  | 2058 MB| 584 kB mhrordhu_sak_os | form7_khata_audit  | 2042 MB| 576 kB mhrordhu_dhu_his  | holder_detail  | 1963 MB| 552 kB mhrordhu_dhu_os | holder_detail_audit_cor| 1605 MB| 464 kB<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>12. Size of all tables , table related objects size and total table size in a current schema or any schema</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">SELECT stats.relname AS table, pg_size_pretty(pg_relation_size(statsio.relid)) AS table_size, pg_size_pretty(pg_total_relation_size(statsio.relid)- pg_relation_size(statsio.relid)) AS related_objects_size, pg_size_pretty(pg_total_relation_size(statsio.relid)) AS total_table_size, stats.n_live_tup AS live_rows  FROM pg_catalog.pg_statio_user_tables AS statsio  JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schema  -- Replace with any schema name UNION ALLSELECT 'TOTAL' AS table, pg_size_pretty(sum(pg_relation_size(statsio.relid))) AS table_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid)- pg_relation_size(statsio.relid))) AS related_objects_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid))) AS total_table_size, sum(stats.n_live_tup) AS live_rows  FROM pg_catalog.pg_statio_user_tables AS statsio  JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schema  -- Replace with any schema name ORDER BY live_rows ASC;  table | table_size | related_objects_size | total_table_size | live_rows------------------+------------+----------------------+------------------+----------- t11  | 0 bytes| 0 bytes  | 0 bytes  | 0 t1 | 0 bytes| 16 kB| 16 kB| 0 m_offic_temp | 8192 bytes | 32 kB| 40 kB| 3 temptbl  | 8192 bytes | 32 kB| 40 kB|41 form7_khata_temp | 1568 kB| 32 kB| 1600 kB  |  8507 temp | 0 bytes| 8192 bytes | 8192 bytes |119340 temp | 0 bytes| 8192 bytes | 8192 bytes |119340 temp | 25 MB  | 32 kB| 25 MB|119340 temp | 0 bytes| 8192 bytes | 8192 bytes |119340 temp | 0 bytes| 8192 bytes | 8192 bytes |119340 TOTAL| 27 MB  | 176 kB | 27 MB|605251<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>Following meta-command is also useful for listing the existing tables size in a current schema</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">nellore=# \d+List of relations Schema | Name | Type  |  Owner  |  Size | Description--------+------------------+-------+-----------------+---------+------------- public | form7_khata_temp | table | postgres| 1600 kB | public | temp | table | postgres| 25 MB | public | temptbl  | table | raj_admin | 40 kB |(3 rows)<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>Following meta-command is useful To show tables size of all schema</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">\dt+ *.*<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>Following meta-command is useful To show tables size for a particular schema</b></span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">\dt+ schema_name.*<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"><b>13.PostgreSQL column value size</b><br>To find how much space that needs to store a specific value, you use the pg_column_size() function, for examples:</span></span><br><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">nijam=# select pg_column_size(5::smallint); pg_column_size----------------  2(1 row)</pre><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">nijam=# select pg_column_size(5::int); pg_column_size----------------  4(1 row)</pre><pre class="SYNOPSIS" style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; overflow: auto; padding: 10.5px; transition: all 0.05s ease; word-break: break-all; word-wrap: break-word;">nijam=# select pg_column_size(5::bigint); pg_column_size----------------  8(1 row)<span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></span></pre><span style="font-size: small;"><span style="font-family: "Trebuchet MS", sans-serif;">In this tutorial, you have learned various handy functions and script to get the size of a database, a table, indexes, a tablespace, a schema and a value.</span></span>

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

In this post, I am sharing few important function for finding the size of database, table and index in PostgreSQL. Finding object size in po...

+ Read more »

PostgreSQL 10 Installation

<div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">PostgreSQL 10.3 installation steps:</span><br>In this tutorial i will explain how to install postgreSQL 10 on Red Hat Enterprise Linux Server release 7.3 Beta (Maipo) and How to make network configuration and how to do linux partition for DATA , ARCHIVE, BACKUP.</div><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Step 1. Download the rpm from below links</span><br><a href="https://yum.postgresql.org/10/redhat/rhel-7-x86_64/repoview/" style="background-color: transparent; box-sizing: border-box; margin: 0px; padding: 0px; text-decoration-line: none; transition: all 0.3s ease 0s;">https://yum.postgresql.org/10/redhat/rhel-7-x86_64/repoview/</a><br>(OR)<br><a href="http://www.agussyarif.com/2017/11/oracle-and-postgresql-dba-related.html" style="background-color: transparent; box-sizing: border-box; margin: 0px; padding: 0px; text-decoration-line: none; transition: all 0.3s ease 0s;">http://www.agussyarif.com/2017/11/oracle-and-postgresql-dba-related.html</a><br>(OR)<br><a href="https://yum.postgresql.org/rpmchart.php" style="background-color: transparent; box-sizing: border-box; margin: 0px; padding: 0px; text-decoration-line: none; transition: all 0.3s ease 0s;">https://yum.postgresql.org/rpmchart.php </a></div><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Step 2. </span><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Install the below mandatory rpm’s .</span><br><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">1.postgresql10-libs</span> – The shared libraries required for any PostgreSQL clients<br><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">2.postgresql10</span> – PostgreSQL client programs and libraries<br><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">3.postgresql10-server</span> – The programs needed to create and run a PostgreSQL server<br><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">4.postgresql10-contrib</span> – Contributed source and binaries distributed with PostgreSQL</div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 230.4px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">rpm -ivh postgresql1010-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-contrib-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-contrib-debuginfo-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-debuginfo-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-debugsource-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-devel-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-devel-debuginfo-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-docs-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-libs-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-libs-debuginfo-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-pglogical-2.2.2-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-pglogical-debuginfo-2.2.2-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-pglogical-debugsource-2.2.2-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-plperl-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-plperl-debuginfo-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-plpython-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-plpython-debuginfo-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-pltcl-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-pltcl-debuginfo-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-server-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-server-debuginfo-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-test-10.10-1.x86_64.rpm --nodepsrpm -ivh postgresql1010-test-debuginfo-10.10-1.x86_64.rpm --nodeps</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;"><br></span><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;"><br></span><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Step 3. Intialize  the  postgres cluster .</span></div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 76.8px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[root@master bin]# pwd/usr/pgsql-10/bin[root@master bin]# ls -lrt postgresql-10-setup-rwxr-xr-x. 1 root root 8582 Feb 27 05:16 postgresql-10-setup</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Then initialize the database:</span></div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 38.4px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[root@master bin]# /usr/pgsql-10/bin/postgresql-10-setup initdbInitializing database ... OK</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Note</span>:PostgreSQL data is typically found /var/lib/pgsql/10/data/ directory.<br><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;"></span></div><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Verify the data directory:</span></div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 76.8px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[root@master bin]# /var/lib/pgsql/10/data/base/ pg_commit_ts/ pg_multixact/ pg_serial/pg_stat_tmp/  pg_twophase/global/ pg_dynshmem/  pg_notify/pg_snapshots/ pg_subtrans/  pg_wal/log/  pg_logical/ pg_replslot/  pg_stat/  pg_tblspc/pg_xact/</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Step 4. Currently postgres is not running we need to start the postgres cluster:</span><br>A.check the postgres cluster status:</div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 38.4px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[root@master bin]# ps -ef|grep postgresroot 5141 3519  0 18:41 pts/000:00:00 grep --color=auto postgres</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;">B.start the postgres cluster:</div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 324px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[root@master ]# systemctl start postgresql-10[root@master ]# ps -ef|grep postroot 2271  1  0 17:21 ?00:00:00 /usr/libexec/postfix/master -wpostfix2274 2271  0 17:21 ?00:00:00 pickup -l -t unix -upostfix2275 2271  0 17:21 ?00:00:00 qmgr -l -t unix -uroot 5171 3519  0 18:44 pts/000:00:00 su postgrespostgres 5172 5171  0 18:44 pts/000:00:00 bashpostgres 5295  1  0 18:47 ?00:00:00 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data/postgres 5297 5295  0 18:47 ?00:00:00 postgres: logger process postgres 5299 5295  0 18:47 ?00:00:00 postgres: checkpointer process postgres 5300 5295  0 18:47 ?00:00:00 postgres: writer process postgres 5301 5295  0 18:47 ?00:00:00 postgres: wal writer process postgres 5302 5295  0 18:47 ?00:00:00 postgres: autovacuum launcher process postgres 5303 5295  0 18:47 ?00:00:00 postgres: stats collector process postgres 5304 5295  0 18:47 ?00:00:00 postgres: bgworker: logical replication launcher root 5306 5237  0 18:47 pts/000:00:00 grep --color=auto post</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">C. Check the database connectivity:</span></div><div class="eaa-wrapper eaa_post_between_content eaa_desktop" id="eaa_post_between_content" style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin: 0px; padding: 0px;"><div class="eaa-ad  " style="box-sizing: border-box; margin: 0px; padding: 0px;"><ins class="adsbygoogle" data-ad-client="pub-7042098537790045" data-ad-format="auto" data-adsbygoogle-status="done" data-full-width-responsive="true" style="box-sizing: border-box; display: block; height: 0px; margin: 0px; padding: 0px;"><ins id="aswift_1_expand" style="background-color: transparent; border: none; box-sizing: border-box; display: inline-table; height: 0px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 700px;"><ins id="aswift_1_anchor" style="background-color: transparent; border: none; box-sizing: border-box; display: block; height: 0px; margin: 0px; opacity: 0; overflow: hidden; padding: 0px; position: relative; visibility: visible; width: 700px;"><iframe allowfullscreen="true" allowtransparency="true" frameborder="0" height="280" hspace="0" id="aswift_1" marginheight="0" marginwidth="0" name="aswift_1" scrolling="no" style="border-style: initial; border-width: 0px; box-sizing: border-box; height: 280px; left: 0px; margin: 0px; max-width: 100%; overflow: visible; padding: 0px; position: absolute; top: 0px; width: 700px;" vspace="0" width="700"></iframe></ins></ins></ins></div></div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 268.8px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[root@master ]# psqlpsql: FATAL:  role "root" does not exist[root@master ]# su postgres[postgres@master root]$ cd[postgres@master ]$ psqlpsql (10.3)Type "help" for help.postgres=# show data_directory; data_directory ------------------------ /var/lib/pgsql/10/data(1 row)</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;"><b style="color: #201f20; font-family: "trebuchet ms", sans-serif;">For More info about <a href="https://www.agussyarif.com/2019/09/postgresql-database-startup-shutdown.html" style="background: transparent; color: #e76d80; text-decoration-line: none;">postgresql server start|stop|reload|restart</a></b></span></div><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Step 5.  Changing data directory path.</span></div><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;">A. Stop the postgres server .</div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 57.6px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[postgres@master ]$ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ stop waiting for server to shut down.... done server stopped</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;">B. Move the Data’s  to /mnt/DATA partition:</div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 326.4px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[postgres@master ]$ rsync -av /var/lib/pgsql/10/data/* /mnt/DATA/sending incremental file listPG_VERSIONcurrent_logfilespg_hba.confpg_ident.confpostgresql.auto.confpostgresql.confpostmaster.optsbase/base/1/base/1/112......sent 41370578 bytes  received 18299 bytes  16555550.80 bytes/sectotal size is 41316241  speedup is 1.00</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;">C.  Check the data directory on postgresql.conf file</div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 38.4px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[postgres@slave1 ]$ cat /mnt/DATA/postgresql.conf |grep data_directory#data_directory = 'ConfigDir'  # use data in another directory</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">As postgres user:</span><br>change the data partition ownership.</div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 112.8px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[postgres@master DATA]$ /usr/pgsql-10/bin/pg_ctl -D /mnt/DATA startwaiting for server to start....2018-03-06 19:18:05.013 IST [5745] FATAL:  data directory "/mnt/DATA" has group or world access2018-03-06 19:18:05.013 IST [5745] DETAIL:  Permissions should be u=rwx (0700). stopped waitingpg_ctl: could not start server</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;">Examine the log output.</div><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Solution:</span><br>changing data partion permission as 700 then start the postgres cluster.</div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 208.8px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[postgres@master ]$  chmod 0700 /mnt/DATA/[postgres@master ]$ /usr/pgsql-10/bin/pg_ctl -D /mnt/DATA startwaiting for server to start....2018-03-06 19:22:47.515 IST [5920] LOG:  listening on IPv6 address "::1%1", port 54322018-03-06 19:22:47.515 IST [5920] LOG:  listening on IPv4 address "127.0.0.1", port 54322018-03-06 19:22:47.517 IST [5920] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"2018-03-06 19:22:47.521 IST [5920] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"2018-03-06 19:22:47.660 IST [5920] LOG:  redirecting log output to logging collector process2018-03-06 19:22:47.660 IST [5920] HINT:  Future log output will appear in directory "log". doneserver started</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><br></div><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;">Checking postgres process whether is running or not.</div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 343.2px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[postgres@master DATA]$ ps -ef|grep postgresroot 5171 3519  0 18:44 pts/000:00:00 su postgrespostgres 5172 5171  0 18:44 pts/000:00:00 bashroot 5317 5237  0 18:48 pts/000:00:00 su postgrespostgres 5318 5317  0 18:48 pts/000:00:00 bashroot 5876 5843  0 19:22 pts/000:00:00 su postgrespostgres 5877 5876  0 19:22 pts/000:00:00 bashpostgres 5920  1  0 19:22 pts/000:00:00 /usr/pgsql-10/bin/postgres -D /mnt/DATApostgres 5921 5920  0 19:22 ?00:00:00 postgres: logger process postgres 5923 5920  0 19:22 ?00:00:00 postgres: checkpointer process postgres 5924 5920  0 19:22 ?00:00:00 postgres: writer process postgres 5925 5920  0 19:22 ?00:00:00 postgres: wal writer process postgres 5926 5920  0 19:22 ?00:00:00 postgres: autovacuum launcher process  postgres 5927 5920  0 19:22 ?00:00:00 postgres: stats collector process postgres 5928 5920  0 19:22 ?00:00:00 postgres: bgworker: logical replication launcher  postgres 5980 5877  0 19:28 pts/000:00:00 ps -efpostgres 5981 5877  0 19:28 pts/000:00:00 grep --color=auto postgres</code><div class="toolbar" style="box-sizing: border-box; margin: 0px; opacity: 0; padding: 0px; position: absolute; right: 0.2em; top: 0.3em; transition: opacity 0.3s ease-in-out 0s;"><div class="toolbar-item" style="box-sizing: border-box; display: inline-block; margin: 0px; padding: 0px;"><a href="https://draft.blogger.com/u/1/null" style="background: rgba(224, 224, 224, 0.2); border-radius: 0.5em; box-shadow: rgba(0, 0, 0, 0.2) 0px 2px 0px 0px; box-sizing: border-box; cursor: pointer; font-size: 0.8em; margin: 0px; padding: 0px 0.5em; transition: all 0.3s ease 0s;">Copy</a></div></div></pre><div style="background-color: white; box-sizing: border-box; font-family: Roboto; font-size: 15px; margin-bottom: 20px; padding: 0px;"><span style="box-sizing: border-box; font-weight: 700; margin: 0px; padding: 0px;">Step 6: then put the application details on pg_hba.conf file the reload or restart the postgres server then check the postgres connectivity and check the data partition whether is updated or not in data_directory parameter.</span></div><pre class="line-numbers language-markup code-toolbar" style="background: linear-gradient(transparent 50%, rgba(69, 142, 209, 0.04) 50%) 0% 0% / 3em 3em content-box content-box rgb(253, 253, 253); border-color: rgb(204, 204, 204) rgb(204, 204, 204) rgb(204, 204, 204) rgb(53, 140, 203); border-image: initial; border-radius: 4px; border-style: solid; border-width: 1px 1px 1px 10px; box-shadow: rgb(53, 140, 203) -1px 0px 0px 0px, rgb(223, 223, 223) 0px 0px 0px 1px; box-sizing: border-box; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: 13px; hyphens: none; line-height: 1.5; margin-bottom: 1em; margin-top: 0.5em; overflow-wrap: normal; overflow: visible; padding: 0px; position: relative; tab-size: 4; word-break: normal;"><code class=" language-markup" style="background: none; border-radius: 0px; box-sizing: border-box; display: block; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; font-size: inherit; height: 192px; hyphens: none; line-height: 1.5; margin: 0px; max-height: inherit; overflow-wrap: normal; overflow: auto; padding: 0px 1em 0px 3.8em; tab-size: 4; word-break: normal; word-spacing: normal;">[postgres@master ]$ psql psql (10.3) Type "help" for help. postgres=# postgres=# show data_directory; data_directory ---------------- /mnt/DATA (1 row)</code></pre>

PostgreSQL 10 Installation

PostgreSQL 10.3 installation steps: In this tutorial i will explain how to install postgreSQL 10 on Red Hat Enterprise Linux Server release ...

+ Read more »

PostgreSQL SKIP LOCKED

<h2 style="background: none rgb(255, 255, 255); border-bottom: 1px solid rgb(170, 170, 170); font-family: sans-serif; font-weight: normal; margin: 0px 0px 0.6em; padding-bottom: 0.17em; padding-top: 0.5em;"><span style="font-size: small;">If you need to SELECT from a table and protect those rows from being updated until your transaction has completed, you would specify FOR UPDATE, but if some rows are locked, you can specify SKIP LOCKED to tell it to simply ignore those rows and just perform the operation on whichever rows it can access.</span></h2><div style="background-color: white; font-family: sans-serif; font-size: 12.7px; line-height: 1.5em; margin-bottom: 0.5em; margin-top: 0.4em;">For example:</div><div style="background-color: white; font-family: sans-serif; font-size: 12.7px; line-height: 1.5em; margin-bottom: 0.5em; margin-top: 0.4em;"><b>In session 1:</b></div><pre style="background-color: #f9f9f9; border: 1px dashed rgb(47, 111, 171); line-height: 1.1em; padding: 1em;"> # BEGIN; BEGIN</pre><pre style="background-color: #f9f9f9; border: 1px dashed rgb(47, 111, 171); line-height: 1.1em; padding: 1em;"> # SELECT * FROM colours;  id | name ----+------- 1 | red 2 | green 3 | blue (3 rows)</pre><pre style="background-color: #f9f9f9; border: 1px dashed rgb(47, 111, 171); line-height: 1.1em; padding: 1em;"> # UPDATE colours SET name = 'scarlet' WHERE name = 'red'; CREATE TABLE</pre><div style="background-color: white; font-family: sans-serif; font-size: 12.7px; line-height: 1.5em; margin-bottom: 0.5em; margin-top: 0.4em;"><b>In session 2:</b></div><pre style="background-color: #f9f9f9; border: 1px dashed rgb(47, 111, 171); line-height: 1.1em; padding: 1em;"> # SELECT * FROM colours FOR UPDATE NOWAIT; ERROR:  could not obtain lock on row in relation "colours"</pre><pre style="background-color: #f9f9f9; border: 1px dashed rgb(47, 111, 171); line-height: 1.1em; padding: 1em;"> # SELECT * FROM colours FOR UPDATE <b>SKIP LOCKED</b>;  id | name ----+------- 2 | green 3 | blue (2 rows)</pre>

PostgreSQL SKIP LOCKED

If you need to SELECT from a table and protect those rows from being updated until your transaction has completed, you would specify FOR UPD...

+ Read more »

PostgreSQL Sequence

A <i class="EMPHASIS">sequence</i> in PostgreSQL is a database object that is essentially an automatically incrementingnumeric value. For this reason, sequences are commonly known in other database products as <i class="EMPHASIS">auto-increment</i>values. Sequences can be extremely useful in assigning non-random, unique identification numbers to tables that requiresuch values.A sequence consists of a current numeric value, and a set of characteristics that determine how to automatically increment(or alternatively, decrement) that value upon use.<br>Along with its current value, a sequence also includes a minimum value, a maximum value, a starting value, and the<i class="EMPHASIS">amount</i> to increment the sequence by. This increment is usually 1, but may beany whole integer.<br>In practice, sequences are not meant to be accessed directly. Instead, they are used through a set of functions builtinto PostgreSQL which either set, increment, or return the current value of the sequence.<br><div class="SECT3"><h3 class="SECT3">Creating a sequence</h3>Sequences are created with the <tt class="LITERAL">CREATE SEQUENCE</tt> SQL command. The sequence can bespecified to increment or decrement. The syntax for <tt class="LITERAL">CREATE SEQUENCE</tt> is:<br><pre class="SCREEN">  CREATE SEQUENCE <tt class="REPLACEABLE"><i>sequencename</i></tt> [ INCREMENT <tt class="REPLACEABLE"><i>increment</i></tt> ] [ MINVALUE <tt class="REPLACEABLE"><i>minvalue</i></tt> ] [ MAXVALUE <tt class="REPLACEABLE"><i>maxvalue</i></tt> ] [ START <tt class="REPLACEABLE"><i>start</i></tt> ] [ CACHE <tt class="REPLACEABLE"><i>cache</i></tt> ] [ CYCLE ]</pre>In this syntax, <tt class="REPLACEABLE"><i>sequencename</i></tt> is the name of the sequence to be created. This is the onlyrequired parameter. A sequence uses the integer data type, and it therefore shares its maximum and minimum limitations of2147483647 and –2147483647, respectively.<br>The optional <tt class="LITERAL">CREATE SEQUENCE</tt> clauses are as follows:<br><div class="VARIABLELIST"><dl><dt><tt class="LITERAL">INCREMENT <tt class="REPLACEABLE"><i>increment_val</i></tt></tt></dt><dd>Sets the numeric quantity with which to modify the sequence's value to <tt class="REPLACEABLE"><i>increment_val</i></tt>. This isused when the <tt class="LITERAL">nextval()</tt> function is called on the sequence. Setting<tt class="REPLACEABLE"><i>increment_val</i></tt> to a negative number results in a descending sequence. The default value is 1.</dd><dt><tt class="LITERAL">MINVALUE <tt class="REPLACEABLE"><i>minvalue</i></tt></tt></dt><dd>Sets the fixed minimum value for the sequence to <tt class="REPLACEABLE"><i>minvalue</i></tt>. Any attempt to lower asequence below this value will result in an error, or in the value cycling to its maximum value (ifthe <tt class="LITERAL">CYCLE</tt> keyword was used when the sequence was created).<br>The default value is 1 for ascending sequences, and –2147483647 for descending sequences.</dd><dt><tt class="LITERAL">MAXVALUE <tt class="REPLACEABLE"><i>maxvalue</i></tt></tt></dt><dd>Sets the fixed maximum value for the sequence to <tt class="REPLACEABLE"><i>maxvalue</i></tt>. Any attempt to raisea sequence above this value will result in an error, or in the value cycling to its minimum value.<br>The default value is 2147483647 for ascending sequences, and –1 for descending sequences.</dd><dt><tt class="LITERAL">START <tt class="REPLACEABLE"><i>start_val</i></tt></tt></dt><dd>Sets the value that the sequence begins at. It may be any integer between the minimum and maximum values. The sequence defaults to start at its minimum value for ascending sequences, and its maximum value for descendingsequences.</dd><dt><tt class="LITERAL">CACHE <tt class="REPLACEABLE"><i>cache</i></tt></tt></dt><dd>Provides the ability for sequence values to be pre-allocated and stored in memory. This can resultin faster access times to highly used sequences. The minimum and default value is 1; a highervalue of <tt class="REPLACEABLE"><i>cache</i></tt> results in more values being cached.</dd><dt><tt class="LITERAL">CYCLE</tt></dt><dd>Enables the sequence to continue generating new values after it has reached its maximum or minimum value. When thelimit is reached, the sequence starts over at the minimum value (for ascending sequences), or at the maximum value (descendingsequences).</dd></dl></div>Example 7-28 creates a simple ascending sequence named<tt class="LITERAL">shipments_ship_id_seq</tt> that starts at a value of 0, and will beincremented by the default increment of 1 until it reaches the default maximum limit of2147483647. By not using the <tt class="LITERAL">CYCLE</tt> keyword, the sequence isguaranteed to always return a unique value.<br><div class="EXAMPLE"><b>Example 7-28. Creating a sequence</b><br><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">postgres=# CREATE SEQUENCE shipments_ship_id_seq MINVALUE 0;CREATE SEQUENCE</pre></div></div><div class="SECT3"><h3 class="SECT3">Viewing a sequence</h3>The output from the <tt class="LITERAL">\d</tt> command within <i class="EMPHASIS">psql</i> shows whether ornot a database object is a sequence, table, view or index. More specifically, the <tt class="LITERAL">\ds</tt>command can be used to view all sequences in the currently connected database. For example:<br><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">postgres=# \ds  List of relations Schema | Name  | Type |  Owner--------+-----------------------+----------+---------- public | shipments_ship_id_seq | sequence | postgres(1 row)</pre>While not often necessary, sequences can be directly queried with <tt class="LITERAL">SELECT</tt>statements, as if they were a table or view.When you query a sentence, you use the attributes of that sequence as columns in your select list.The attributes of a sequence are shown in Table 7-1.<br><div class="TABLE"><b>Table 7-1. Sequence attributes</b><br><br><br><table border="1" cellpadding="0" class="MsoNormalTable" style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext; mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184;"> <thead><tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-family: "times new roman" , serif; font-size: 12.0pt;">Attribute</span></b></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-family: "times new roman" , serif; font-size: 12.0pt;">Type</span></b></div></td>  </tr><tr style="mso-yfti-irow: 1;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">sequence_name</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">name</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr><tr style="mso-yfti-irow: 2;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">last_value</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">integer</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr><tr style="mso-yfti-irow: 3;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">increment_by</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">integer</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr><tr style="mso-yfti-irow: 4;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">max_value</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">integer</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr><tr style="mso-yfti-irow: 5;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">min_value</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">integer</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr><tr style="mso-yfti-irow: 6;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">cache_value</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">integer</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr><tr style="mso-yfti-irow: 7;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">log_cnt</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">integer</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr><tr style="mso-yfti-irow: 8;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">is_cycled</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">"char"</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr><tr style="mso-yfti-irow: 9; mso-yfti-lastrow: yes;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 101.25pt;" valign="top" width="138"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">is_called</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 51.75pt;" valign="top" width="72"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">"char"</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr></thead></table><br><b> </b>Example 7-29 illustrates a query to the <tt class="LITERAL">shipments_ship_id_seq</tt>sequence. This query selects the <tt class="LITERAL">last_value</tt> attribute, which is the most currentlyselected value from the sequence, and the <tt class="LITERAL">increment_by</tt> attribute, which is the amount thesequence is to be incremented each time the <tt class="LITERAL">nextval()</tt> function is called.</div><div class="EXAMPLE"><b>Example 7-29. Viewing a sequence</b><br><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">postgres=# \d shipments_ship_id_seq Sequence "public.shipments_ship_id_seq"  Type  | Start | Minimum | Maximum | Increment | Cycles? | Cache--------+-------+---------+---------------------+-----------+---------+------- bigint | 0 | 0 | 9223372036854775807 | 1 | no  | 1</pre><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">postgres=# select *from shipments_ship_id_seq; last_value | log_cnt | is_called------------+---------+-----------  0 | 0 | f(1 row)</pre></div>Since the sequence in question has just been created, its <tt class="LITERAL">last_value</tt> is still set to0.</div><div class="SECT3"><h3 class="SECT3">Using a sequence</h3>Sequences are typically not queried directly, but are instead used through functions. There are three functions inPostgreSQL which apply exclusively to sequences:<br><div class="VARIABLELIST"><dl><dt><tt class="LITERAL">nextval('<tt class="REPLACEABLE"><i>sequence_name</i></tt>')</tt></dt><dd>Increments the value of the specified sequence named <tt class="REPLACEABLE"><i>sequence_name</i></tt>, and returns the new value,which is of type <tt class="LITERAL">integer</tt>.</dd><dt><tt class="LITERAL">currval('<tt class="REPLACEABLE"><i>sequence_name</i></tt>')</tt></dt><dd>Returns the most recently returned value from <tt class="LITERAL">nextval('</tt><tt class="REPLACEABLE"><i>sequence_name</i></tt><tt class="LITERAL">')</tt>. This valueis associated with a PostgreSQL session, and if the <tt class="LITERAL">nextval()</tt> function has not yetbeen called in the connected session on the specified sequence <tt class="REPLACEABLE"><i>sequence_name</i></tt>, there will be no"current" value returned.</dd><dt><tt class="LITERAL">setval('<tt class="REPLACEABLE"><i>sequence_name</i></tt>', <tt class="REPLACEABLE"><i>n</i></tt> )</tt></dt><dd>Sets the current value of the specified sequence to the numeric value <tt class="REPLACEABLE"><i>n</i></tt>.The value returned by the next call to <tt class="LITERAL">nextval()</tt> will return<tt class="REPLACEABLE"><i>n + increment</i></tt>, where <tt class="REPLACEABLE"><i>increment</i></tt> is the amount that the sequence incrementsby each iteration.</dd><dt><tt class="LITERAL">setval('<tt class="REPLACEABLE"><i>sequence_name</i></tt>', <tt class="REPLACEABLE"><i>n</i></tt>, <tt class="REPLACEABLE"><i>b</i></tt> )</tt></dt><dd>Also sets the current value of the specified sequence to the numeric value <tt class="REPLACEABLE"><i>n</i></tt>. However, if<tt class="REPLACEABLE"><i>b</i></tt> (a value of type <tt class="LITERAL">boolean</tt>) is <i class="EMPHASIS">false</i>,the value returned by the next call to <tt class="LITERAL">nextval()</tt> will be just<tt class="REPLACEABLE"><i>n</i></tt> . If <tt class="REPLACEABLE"><i>b</i></tt> is <i class="EMPHASIS">true</i>, the next call to<tt class="LITERAL">nextval()</tt> will return <i class="EMPHASIS">n + increment</i>, as it would withoutspecifying the Boolean argument at all.</dd></dl></div>The most commonly used sequence function is <tt class="LITERAL">nextval()</tt>. This is the function thatactually pushes the increment of the value. It requires the name of the sequence as the argument (bound by singlequotes), and returns a value of type <tt class="LITERAL">integer</tt>.<br>Example 7-30 selects a couple of incremented values from the sequence named<tt class="LITERAL">shipments_ship_id_seq</tt>.<br><div class="EXAMPLE"><b>Example 7-30. Incrementing a sequence</b><br><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">postgres=# SELECT nextval('shipments_ship_id_seq'); nextval--------- 0(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval--------- 1(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval--------- 2(1 row)</pre></div><div class="NOTE"><blockquote class="NOTE"><b>Note: </b>The first call to <tt class="LITERAL">nextval()</tt> will return the sequence's <i class="EMPHASIS">initial</i> value(set by the <tt class="LITERAL">START</tt> keyword), since it has not yet been called to increment the startingvalue. All subsequent calls increment the <tt class="LITERAL">last_value</tt> column.</blockquote></div>Sequences are commonly used as default values for tables which require unique integer identifiers. The<tt class="LITERAL">shipments</tt> table within the <tt class="LITERAL">booktown</tt> database, shown inTable 7-2, exemplifies this.<br><div class="TABLE"><b>Table 7-2. The shipments table</b><br><b><table border="1" cellpadding="0" class="MsoNormalTable" style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext; mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184;"> <thead><tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.5pt;" valign="top" width="108"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-family: "times new roman" , serif; font-size: 12.0pt;">Column</span></b></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.45pt;" valign="top" width="107"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-family: "times new roman" , serif; font-size: 12.0pt;">Type</span></b></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 192.3pt;" valign="top" width="262"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-family: "times new roman" , serif; font-size: 12.0pt;">Modifier</span></b></div></td>  </tr><tr style="mso-yfti-irow: 1;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.5pt;" valign="top" width="108"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">id</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.45pt;" valign="top" width="107"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">integer</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 192.3pt;" valign="top" width="262"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">NOT NULL DEFAULT nextval('shipments_ship_id_seq')</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td>  </tr><tr style="mso-yfti-irow: 2;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.5pt;" valign="top" width="108"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">customer_id</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.45pt;" valign="top" width="107"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">integer</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 192.3pt;" valign="top" width="262"><br></td>  </tr><tr style="mso-yfti-irow: 3;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.5pt;" valign="top" width="108"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">isbn</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.45pt;" valign="top" width="107"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">text</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 192.3pt;" valign="top" width="262"><br></td>  </tr><tr style="mso-yfti-irow: 4; mso-yfti-lastrow: yes;"> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.5pt;" valign="top" width="108"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">ship_date</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 78.45pt;" valign="top" width="107"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "courier new"; font-size: 10.0pt;">timestamp with time zone</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt; width: 192.3pt;" valign="top" width="262"><br></td>  </tr></thead></table> </b><br><b> </b>The syntax to create the table in Table 7-2, with the auto-incrementing<tt class="LITERAL">DEFAULT</tt> and <tt class="LITERAL">PRIMARY KEY</tt> constraint, is:</div><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">  CREATE TABLE shipments (id integer DEFAULT nextval('"shipments_ship_id_seq"'::text) PRIMARY KEY,  customer_id integer,  isbn text,  ship_date timestamp);</pre>The default value for the <tt class="LITERAL">id</tt> column in Table 7-2 is setto the <tt class="LITERAL">nextval()</tt>'s result on the <tt class="LITERAL">shipments_ship_id_seq</tt>sequence. Insertion of row data that does not specify a value for <tt class="LITERAL">id</tt>will therefore choose its value from the result of this function call.<br><table border="1" cellpadding="0" class="MsoNormalTable" style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext; mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184; width: 100%px;"> <tbody><tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;">  <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt;">  <div align="center" class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; text-align: center;"><b><span style="font-family: "Times New Roman",serif; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Warning</span></b><span style="font-family: "Times New Roman",serif; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div></td> </tr><tr style="mso-yfti-irow: 1; mso-yfti-lastrow: yes;">  <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt;">  <div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman",serif; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Merely placing a </span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">DEFAULT</span><span style="font-family: "Times New Roman",serif; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> constraint  on the </span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">id</span><span style="font-family: "Times New Roman",serif; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">  column does not enforce the use of that default. A user could still manually  insert a value, potentially causing a conflict with future sequence values.  This can be disallowed with the use of a trigger. See the Section called <i>Triggers</i>  " later in this chapter for more information.</span></div></td> </tr></tbody></table><br>After the <tt class="LITERAL">nextval()</tt> function has been called on a sequence in a given session(a connection to PostgreSQL), the <tt class="LITERAL">currval()</tt> function may be used on that same sequence toreturn the most recently returned value from the sequence. Note that this function may <i class="EMPHASIS">only</i> becalled on a sequence that has been called through <tt class="LITERAL">nextval()</tt> in the activesession.<br><div class="NOTE"><blockquote class="NOTE"><b>Note: </b>Sequences' "current" values are associated with sessions in order to prevent multiple users from running intomistakes by accessing the same sequence at the same time. Two users may access the same sequence from separatesessions, but the <tt class="LITERAL">currval()</tt> function will return only the most recently incrementedvalue of the sequence from within <i class="EMPHASIS">the same session</i> that calls <tt class="LITERAL">currval()</tt>.</blockquote></div>Example 7-31 inserts a new row into the <tt class="LITERAL">shipments</tt> column, withoutspecifying the value for the <tt class="LITERAL">id</tt> column. This causes the default value to be used, which(as noted in Table 7-2) is the result of the<tt class="LITERAL">shipments_ship_id_seq</tt> being incremented by the<tt class="LITERAL">nextval()</tt> function. The <tt class="LITERAL">currval()</tt> function is thenused to access the row that was just inserted.<br><div class="EXAMPLE"><b>Example 7-31. Using currval( )</b><br><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">postgres=# INSERT INTO shipments (customer_id, isbn, ship_date) VALUES (221, '0394800753', 'now');INSERT 0 1postgres=# SELECT * FROM shipments WHERE id = currval('shipments_ship_id_seq');  id  | customer_id |isbn| ship_date------+-------------+------------+------------------------ 1002 | 107 | 0394800753 | 2001-09-22 11:23:28-07(1 row)</pre></div>Finally, a sequence may also have its <tt class="LITERAL">last_value</tt> attribute reset to an arbitrarynumeric value (within its maximum and minimum value range) by using the <tt class="LITERAL">setval()</tt>function. This requires the name of the sequence as a single-quote bound character string for the first argument and aninteger constant representing the new value for <tt class="LITERAL">last_value</tt> for the second argument.<br>There are two ways to go about this. By default, <tt class="LITERAL">setval()</tt> assumes that the newsetting is for an initialized sequence; this means that the next value returned by<tt class="LITERAL">nextval()</tt> will actually be incremented once past the value set by<tt class="LITERAL">setval()</tt>.<br>Alternatively, an optional <tt class="LITERAL">false</tt> value of type <tt class="LITERAL">boolean</tt> may beadded as the last argument to <tt class="LITERAL">setval()</tt>, de-initializing the sequence. This modifies thesequence so that the next value returned by <tt class="LITERAL">nextval()</tt> will be the same numeric valuepassed to <tt class="LITERAL">setval()</tt> (though the sequence will of course be incremented on the next callto <tt class="LITERAL">nextval()</tt>).<br>Example 7-32 sets the <tt class="LITERAL">shipments_ship_id_seq</tt>'s<tt class="LITERAL">last_value</tt> to 1010 through each method, and selects the<tt class="LITERAL">nextval()</tt> on the same sequence to illustrate the effective result.<br><div class="EXAMPLE"><b>Example 7-32. Setting a sequence value</b><br><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">postgres=# SELECT setval('shipments_ship_id_seq', 1010); setval-------- 1010(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval---------1011(1 row)postgres=# SELECT setval('shipments_ship_id_seq', 1010, false); setval-------- 1010(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval---------1010(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval---------1011(1 row)</pre></div></div><div class="SECT3"><h3 class="SECT3"><table border="1" cellpadding="0" class="MsoNormalTable" style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext; mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184; width: 100%px;"> <tbody><tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;">  <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt;"><div align="center" class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; text-align: center;"><b><span style="font-family: "times new roman" , serif; font-size: 12.0pt;">Warning</span></b><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> </span></div></td> </tr><tr style="mso-yfti-irow: 1; mso-yfti-lastrow: yes;">  <td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: .75pt .75pt .75pt .75pt;"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "times new roman" , serif; font-size: 12.0pt;">Sequences are commonly used to  ensure unique values in a column. Be sure that you understand the application  of a sequence before you reset its </span><span style="font-family: "courier new"; font-size: 10.0pt;">last_value</span><span style="font-family: "times new roman" , serif; font-size: 12.0pt;"> attribute.</span></div></td> </tr></tbody></table></h3><h3 class="SECT3">Destroying a sequence</h3>To destroy a sequence, or several sequences simultaneously, use the <tt class="LITERAL">DROP SEQUENCE</tt>SQL command. Here is the syntax for <tt class="LITERAL">DROP SEQUENCE</tt>:<br><pre class="SCREEN">  DROP SEQUENCE <tt class="REPLACEABLE"><i>sequencename</i></tt> [, ...]</pre>In this syntax, <tt class="REPLACEABLE"><i>sequencename</i></tt> is the name of the sequence that you wish to remove. Multiplesequence names may be specified, separated by commas.<br>Example 7-33 removes the <tt class="LITERAL">shipments_ship_id_seq</tt>sequence.<br><div class="EXAMPLE"><b>Example 7-33. Removing a sequence</b><br><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">postgres=# DROP SEQUENCE shipments_ship_id_seq;DROP SEQUENCE</pre></div>Before destroying a sequence, make sure that the sequence is not used by another table, function, or any otherobject in the database. If this check is not performed, then other operations that rely on the sequence will fail. Thefollowing query will return the name of any relation which relies on a default sequence value, where<tt class="LITERAL">sequence_name</tt> is the name of the sequence you are interesting in finding dependencies for:<br><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">  SELECT p.relname, a.adsrc FROM pg_class p  JOIN pg_attrdef a ON (p.relfilenode = a.adrelid) WHERE a.adsrc  '"sequence_name "';</pre>Example 7-34 uses this query to look up the name of any table with a default valueinvolving the <tt class="LITERAL">shipments_ship_id_seq</tt> sequence.<br><div class="EXAMPLE"><b>Example 7-34. Checking sequence dependencies</b><br><pre class="SYNOPSIS" style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #2080ad; direction: ltr; font-family: monospace, monospace; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; line-height: 1.42857; margin-bottom: 11px; margin-top: 1.53846rem; orphans: 2; overflow: auto; padding: 10.5px; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; transition: all 0.05s ease; widows: 2; word-break: break-all; word-spacing: 0px; word-wrap: break-word;">SELECT p.relname, a.adsrc FROM pg_class p JOIN pg_attrdef aON (p.relfilenode = a.adrelid)WHERE a.adsrc  '"shipments_ship_id_seq"';  relname  |adsrc-----------+------------------------------------------------------ shipments | nextval(('"shipments_ship_id_seq"'::text)::regclass)(1 row)</pre></div></div>

PostgreSQL Sequence

A sequence in PostgreSQL is a database object that is essentially an automatically incrementingnumeric value. For this reason, sequences ar...

+ Read more »

Search This Blog

Featured post

Movies Post

Sports

Music

Business

Pictures

News

Recent Articles

Technology

Famous Posts

Travel

Fashion

Breaking News

Design

Movies

Label 3

Labels

About us

Popular Posts

Populars

Contact Form

Recent Videos

Labels

Footer Label

Accessories[combine]

Photos[carousel](6)

/fa-random/ RANDOM$type=list-tab$date=0$au=0$c=8$src=random-posts

[Music][vertical2][recent][4]

Break News

Simple social

Popular Label

Top Ad unit 728 × 90

[Graphic Design][horizontal][recent][5]

[simplepost][simple][recent][5]

Don't Miss

Social

Video

Accordition

Archive

Comments

Slide Show

Category 4

Featured Posts

728x90 AdSpace

Social Widget

News Ticker

Recent Gallery

Video Of Day

Sale off[right]

Media[left]

Reviews

Categories

$type=blogging$cate=2$count=8$pagination=5

Media

Categories

Category

Category

Slider

Database Insider

Category 1

Recent Post

Category 6

Games

Category 2

Category 7

Pages

Pages

About Us

Find Us On Facebook

Category 5

Random Post

Photo on Flickr

slider

Popular Videos

Featured Posts