PostgreSQL

Modified: 08 Sep 2022 04:28 UTC

The PostgreSQL images provides a preconfigured PostgreSQL environment.

Overview

The PostgreSQL instance is configured with the following user accounts:

You can find passwords for the root and admin accounts in the Credentials section of the Instance Details page in the Joyent portal.

The password listed for the postgres account is the PostgreSQL password for logging into PostgreSQL, not for logging into the SmartOS user account.

PostgreSQL configuration and data files

You can find PostgreSQL data in /var/pgsql/data.

PostgreSQL image comes already tuned by default according to your instance size. If you need to further tune your instance, you can edit the PostgreSQL configuration file /var/pgsql/data/postgresql.conf.

Logging in to PostgreSQL

To log in to PostgreSQL, you must first log in as root, then su as the postgres user. The PostgreSQL password is the one listed in the Credentials section mentioned above:

    [root@63cd30b9-5424-c2b3-8ab7-84c760be958d ~]# su - postgres
       __        .                   .
     _|  |_      | .-. .  . .-. :--. |-
    |_    _|     ;|   ||  |(.-' |  | |
      |__|   `--'  `-' `;-| `-' '  ' `-'
                       /  ; Instance (postgresql 13.3.1)
                       `-'  http://wiki.joyent.com/jpc2/Postgresql+Instance

    -bash-4.1$ psql
    Password:
    psql (9.3.0)
    Type "help" for help.

    postgres=#

You can also execute a single command like this:

    # PGPASSWORD="$(mdata-get pgsql_pw)" psql -U postgres -c "select version();"
                                         version
    ---------------------------------------------------------------------------------
     PostgreSQL 9.3.0 on x86_64-sun-solaris2.11, compiled by gcc (GCC) 4.7.3, 64-bit
    (1 row)

Using PostgreSQL administrator

PostgreSQL image comes with pgadmin support installed by default. To use it download the pgadmin client for your platform from http://www.pgadmin.org/download/.

For a secure connection use SSH tunneling from your local machine to the PostgreSQL instance:

    laptop$ ssh -L5432:<internal.ip.of.instance>:5432 -p 22 -N -t -x admin@<external.ip.of.instance>

Set pgadmin to connect to 127.0.0.1 port 5432.

Installed PostgreSQL modules

postgresql92-monitoring comes with the following modules

You can read about these modules here.

Load the modules like this:

    postgres=# CREATE EXTENSION adminpack;
    postgres=# CREATE EXTENSION pg_buffercache;
    postgres=# CREATE EXTENSION pg_stat_statements;
    postgres=# LOAD 'auto_explain';

Use the dx command to list the loaded modules:

    postgres=# dx
                                         List of installed extensions
            Name        | Version |   Schema   |                        Description
    --------------------+---------+------------+-----------------------------------------------------------
     adminpack          | 1.0     | pg_catalog | administrative functions for PostgreSQL
     pg_buffercache     | 1.0     | public     | examine the shared buffer cache
     pg_stat_statements | 1.1     | public     | track execution statistics of all SQL statements executed
     plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
    (4 rows)

Using DTrace in PostgreSQL instance

PostgreSQL comes with over 50 DTrace probes. You can list them with this command:

    # dtrace -l | grep postgres

You can read descriptions of these probes here.

This image has the dtracetools package installed by default. This package has four DTrace scripts ready to use in /opt/local/bin.

DTrace script Description
dtrace-postgresql_query_counts.d Shows how many of each query was executed
dtrace-postgresql_query_persec.d Shows queries per second
dtrace-postgresql_trx_persec.d Shows transactions per second
dtrace-postgresql_trx_latency.d Shows transaction start/stop times

Configuring a PostgreSQL cluster

Configuring the Master:

Edit the Master PostgreSQL pg_hba.conf in /var/pgsql/data to allow slave nodes to connect to the master.

    host    replication     postgres        x.x.x.x/32           password
    host    replication     postgres        x.x.x.x/23           trust

Edit the Master PostgreSQL postgresql.conf in /var/pgsql/data to add slave nodes to connect to the master, look in the Replication section of the configuration file. Use either IPs or DNS names.

    synchronous_standby_names = 'postgres-2,postgres-3'     # standby servers that provide sync rep
                                    # comma-separated list of application_name
                                    # from standby(s); '*' = all

Restart PostgreSQL Master server:

    svcadm restart svc:/pkgsrc/postgresql:default

Configuring the Slave nodes:

Edit the Salve PostgreSQL pg_hba.conf in /var/pgsql/data to allow the master node to connect to the slaves.

    host    replication     postgres        x.x.x.x/32           password
    host    replication     postgres        x.x.x.x/23           trust

Edit the Slave PostgreSQL postgresql.conf in /var/pgsql/data to allow slave nodes to replicate to the master, look in the Standby Servers section of the configuration file.

    # - Standby Servers -

    hot_standby = on

Restart PostgreSQL Slave server:

    svcadm restart svc:/pkgsrc/postgresql:default

Metadata keys

With the PostgreSQL image are metadata keys that can be pre-defined during provision to provide additional customization to the instance.

Metadata Key Description
pgsql_password PostgreSQL password to set

Version 16.x.x

16.4.1
Name postgresql
Description A SmartOS 64-bit image pre-configured and optimized as a PostgreSQL 9.6.1 64-bit server with PgAdmin and monitoring plugins.
UUID 92a57bb2-ea2b-11e6-8caf-7f81ea0c32cb
Based on base-64-lts 16.4.1
Pkgsrc PKG_PATH=https://pkgsrc.joyent.com/packages/SmartOS/2016Q4/x86_64/All
What's New In This Image
Software Included In This Image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1       Block-sorting file compressor
curl-7.52.1          Client that groks URLs
db4-4.8.30           Berkeley DB version 4 from Oracle
dtracetools-0.7      DTraceTools for Joyent SmartMachines
gcc49-libs-4.9.4nb1  The GNU Compiler Collection (GCC) support shared libraries
gtar-base-1.29       The GNU tape archiver with remote magnetic tape support
htop-2.0.2           Enhanced version of top utility
nodejs-7.2.1         V8 JavaScript for clients and servers
openssl-1.0.2jnb1    Secure Socket Layer and cryptographic library
perl-5.24.0          Practical Extraction and Report Language
pkgin-0.9.4nb4       Apt / yum like tool for managing pkgsrc binary packages
postfix-3.1.3nb1     Postfix SMTP server and tools
postgresql96-9.6.1   Robust, next generation, object-relational DBMS
postgresql96-client-9.6.1 PostgreSQL database client programs
postgresql96-contrib-9.6.1nb1 Contrib subtree of tools and plug-ins
postgresql96-docs-9.6.1 PostgreSQL database documentation
postgresql96-pg_top-3.7.0 Top like tool for PostgreSQL
postgresql96-server-9.6.1 PostgreSQL database server programs
python27-2.7.12nb4   Interpreted, interactive, object-oriented programming language
smtools-20160926     Joyent tools relevant to SmartOS and SmartMachines
sqlite3-3.15.2nb2    SQL Database Engine in a C Library
sudo-1.8.17p1        Allow others to run commands as root
wget-1.18nb3         Retrieve files from the 'net via HTTP and FTP
zoneinit-1.6.9       Joyent Instance initialization toolchain

Software installed as npm packages:

json@9.0.4
manta@4.1.1
smartdc@8.1.0

Version 15.x.x

15.4.1
Name postgresql
Description A SmartOS 64-bit image pre-configured and optimized as a PostgreSQL 9.4.6 64-bit server with PgAdmin and monitoring plugins.
UUID 48bd354a-efd0-11e5-9ae1-d78e67ed4602
Based on base-64-lts 15.4.1
Pkgsrc PKG_PATH=http://pkgsrc.joyent.com/packages/SmartOS/2015Q4/x86_64/All
What's New In This Image
Software Included In This Image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1       Block-sorting file compressor
curl-7.47.1          Client that groks URLs
db4-4.8.30           Berkeley DB version 4 from Oracle
dtracetools-0.7nb20160127 DTraceTools for Joyent SmartMachines
gcc47-libs-4.7.4nb1  The GNU Compiler Collection (GCC) support shared libraries
gcc49-libs-4.9.3nb1  The GNU Compiler Collection (GCC) support shared libraries
gtar-base-1.28nb1    The GNU tape archiver with remote magnetic tape support
htop-1.0.1nb1        Enhanced version of top utility
nodejs-5.7.0         V8 JavaScript for clients and servers
openssl-1.0.2g       Secure Socket Layer and cryptographic library
perl-5.22.0          Practical Extraction and Report Language
pkgin-0.9.4          Apt / yum like tool for managing pkgsrc binary packages
postfix-3.0.2nb2     Postfix SMTP server and tools
postgresql94-9.4.6   Robust, next generation, object-relational DBMS
postgresql94-adminpack-9.4.6 Admin pack module for pgAdmin management
postgresql94-client-9.4.6 PostgreSQL database client programs
postgresql94-datatypes-9.4.6 PostgreSQL data types support modules
postgresql94-docs-9.4.6 PostgreSQL database documentation
postgresql94-monitoring-9.4.6 PostgreSQL monitoring tools
postgresql94-pgbench-9.4.6 PostgreSQL benchmarking tools
postgresql94-pgcrypto-9.4.6 Module providing cryptographic functions for PostgreSQL
postgresql94-server-9.4.6 PostgreSQL database server programs
python27-2.7.11      Interpreted, interactive, object-oriented programming language
smtools-20160316     Joyent tools relevant to SmartOS and SmartMachines
sqlite3-3.9.2        SQL Database Engine in a C Library
sudo-1.8.15          Allow others to run commands as root
wget-1.17.1          Retrieve files from the 'net via HTTP and FTP
zoneinit-1.6.8       Joyent Instance initialization toolchain

Software installed as npm packages:

json@9.0.3
manta@2.0.5
smartdc@8.1.0

15.1.1
Name postgresql
Description A SmartOS 64-bit image pre-configured and optimized as a PostgreSQL 9.3.6 64-bit server with PgAdmin and monitoring
plugins.
UUID e312a72c-0a18-11e5-9a87-9ba4a03d4234
Based on base-64 15.1.1
Pkgsrc 2015Q1
What's New In This Image
Software Included In This Image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1
curl-7.42.0
db4-4.8.30
dtracetools-0.7
gcc47-libs-4.7.4nb1
gtar-base-1.28nb1
htop-1.0.1
nodejs-0.12.4
openssl-1.0.2a
perl-5.20.2
pkgin-0.8.0
postfix-2.11.4
postgresql93-9.3.6
postgresql93-adminpack-9.3.6
postgresql93-client-9.3.6
postgresql93-datatypes-9.3.6
postgresql93-docs-9.3.6
postgresql93-monitoring-9.3.6
postgresql93-pgbench-9.3.6
postgresql93-pgcrypto-9.3.6
postgresql93-server-9.3.6
python27-2.7.9nb1
smtools-20150312
sqlite3-3.8.10
sudo-1.7.10p9
wget-1.16.3
zoneinit-1.6.8

Software installed as npm packages:

json@9.0.3
manta@1.5.1
smartdc@7.3.1

Version 14.x.x

14.4.0
Name postgresql
Description A SmartOS 64-bit image pre-configured and optimized as a PostgreSQL 9.3.5 64-bit server with PgAdmin and monitoring plugins.
UUID fe2b86f4-d486-11e4-8446-4bf28894e502
Based on base-64-lts 14.4.0
Pkgsrc 2014Q4
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1
curl-7.39.0nb1
db4-4.8.30
dtracetools-0.7
gcc47-libs-4.7.3nb7
gtar-base-1.28
htop-1.0.1
nodejs-0.12.1
openssl-1.0.1k
perl-5.20.1
pkgin-0.7.0
postfix-2.11.3
postgresql93-9.3.5
postgresql93-adminpack-9.3.5
postgresql93-client-9.3.5
postgresql93-datatypes-9.3.5nb1
postgresql93-docs-9.3.5
postgresql93-monitoring-9.3.5
postgresql93-pgbench-9.3.5
postgresql93-pgcrypto-9.3.5
postgresql93-server-9.3.5
python27-2.7.9
smtools-20150312
sqlite3-3.8.7.4
sudo-1.7.10p9
wget-1.16.1
zoneinit-1.6.8

Software installed as npm packages:

json@9.0.3
manta@1.5.1
smartdc@7.3.1

14.3.0
Name postgresql
Description A SmartOS 64-bit image pre-configured and optimized as a PostgreSQL 9.3.5 64-bit server with PgAdmin and monitoring plugins.
UUID 2ca7cf08-7be3-11e4-9b45-1f6507130bb7
Based on [base64 14.3.0](SmartMachine Base.html)
Pkgsrc 2014Q3
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.38.0
    db4-4.8.30
    gcc47-libs-4.7.3nb7
    gtar-base-1.28
    nodejs-0.10.33
    openssl-1.0.1j
    perl-5.20.0nb2
    pkgin-0.6.4nb7
    postfix-2.11.1nb1
    postgresql93-9.3.5
    postgresql93-adminpack-9.3.5
    postgresql93-client-9.3.5
    postgresql93-datatypes-9.3.5
    postgresql93-docs-9.3.5
    postgresql93-monitoring-9.3.5
    postgresql93-pgbench-9.3.5
    postgresql93-pgcrypto-9.3.5
    postgresql93-server-9.3.5
    python27-2.7.8nb1
    smtools-20141114
    sqlite3-3.8.6
    sudo-1.7.10p8
    wget-1.16
    zoneinit-1.6.8

Software installed as npm packages:

    manta@1.4.5
    smartdc@7.3.0

14.2.1
Name postgresql
Description A SmartOS 64-bit image pre-configured and optimized as a PostgreSQL 9.3.4 64-bit server with PgAdmin and monitoring plugins.
UUID 46ca6534-53d5-11e4-8fc3-1384eeb2f1c3
Based on [base64 14.2.0](Base Instance.html)
Pkgsrc 2014Q2
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.37.0nb1
    db4-4.8.30
    gcc47-libs-4.7.3nb4
    gtar-base-1.27.1nb1
    nodejs-0.10.29
    openssl-1.0.1h
    perl-5.20.0nb1
    pkgin-0.6.4nb7
    postfix-2.11.1nb1
    postgresql93-9.3.4nb1
    postgresql93-adminpack-9.3.4
    postgresql93-client-9.3.4
    postgresql93-datatypes-9.3.4
    postgresql93-docs-9.3.4nb1
    postgresql93-monitoring-9.3.4
    postgresql93-pgbench-9.3.4
    postgresql93-pgcrypto-9.3.4
    postgresql93-server-9.3.4
    python27-2.7.7nb2
    smtools-20140728
    sqlite3-3.8.5
    sudo-1.7.10p8
    wget-1.15nb2
    zoneinit-1.6.8

Software installed as npm packages:

    jsontool@7.0.2
    manta@1.4.2
    smartdc@7.3.0

14.2.0
Name postgresql
Description A SmartOS 64-bit image pre-configured and optimized as a PostgreSQL 9.3.4 64-bit server with PgAdmin and monitoring plugins.
UUID 8a40ff42-2934-11e4-85fb-f72497b29af4
Based on [base64 14.2.0](Base Instance.html)
Pkgsrc 2014Q2
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.37.0nb1
    db4-4.8.30
    gcc47-libs-4.7.3nb4
    gtar-base-1.27.1nb1
    nodejs-0.10.29
    openssl-1.0.1h
    perl-5.20.0nb1
    pkgin-0.6.4nb7
    postfix-2.11.1nb1
    postgresql93-9.3.4nb1
    postgresql93-adminpack-9.3.4
    postgresql93-client-9.3.4
    postgresql93-datatypes-9.3.4
    postgresql93-docs-9.3.4nb1
    postgresql93-monitoring-9.3.4
    postgresql93-pgbench-9.3.4
    postgresql93-pgcrypto-9.3.4
    postgresql93-server-9.3.4
    python27-2.7.7nb2
    smtools-20140728
    sqlite3-3.8.5
    sudo-1.7.10p8
    wget-1.15nb2
    zoneinit-1.6.8

Software installed as npm packages:

    jsontool@7.0.2
    manta@1.4.2
    smartdc@7.3.0

14.1.0
Name postgresql
Description A SmartOS 64-bit image pre-configured and optimized as a PostgreSQL 9.3.4 64-bit server with PgAdmin and monitoring plugins.
UUID 6d5debc0-dac3-11e3-8bc0-dfc25b68fd97
Based on [base64 14.1.0](Base Instance.html)
Pkgsrc 2014Q1
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.36.0
    db4-4.8.30
    duo-unix-1.9.7
    gcc47-libs-4.7.3nb3
    gtar-base-1.27.1nb1
    libarchive-2.8.4nb3
    nodejs-0.10.26
    openssl-1.0.1g
    perl-5.18.2nb1
    pkgin-0.6.4nb5
    postgresql93-9.3.4
    postfix-2.11.0nb1
    python27-2.7.6nb3
    smtools-20140430
    sqlite3-3.8.4.1
    sudo-1.7.10p8
    wget-1.15nb1
    zoneinit-1.6.7

Software installed as npm packages:

    jsontool@7.0.2
    manta@1.2.6
    smartdc@7.2.1

Version 13.x.x

13.3.1
Name postgresql
Description A SmartOS 64-bit image pre-configured and optimized as a PostgreSQL 9.3.4 64-bit server with PgAdmin and monitoring plugins.
UUID 835e27b2-a47e-11e3-9eb6-e78ef6d1ee8f
Based on [base64 13.3.1](Base Instance.html)
Pkgsrc 2013Q3
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.32.0
    db4-4.8.30
    duo-unix-1.9.6
    gtar-base-1.26nb2
    nodejs-0.10.26
    openssl-1.0.1enb2
    perl-5.18.1nb1
    pkgin-0.6.4nb1
    postfix-2.9.8
    postgresql93-9.3.0
    postgresql93-adminpack-9.3.0
    postgresql93-client-9.3.0
    postgresql93-monitoring-9.3.0
    postgresql93-server-9.3.0
    python27-2.7.5nb3
    smtools-20140303
    sqlite3-3.8.0.2
    sudo-1.7.10p7
    wget-1.14nb3
    zoneinit-1.6.7

Software installed as npm packages:

    jsontool@7.0.1
    manta@1.2.6
    smartdc@7.2.0