PostgreSQL
The PostgreSQL images provides a preconfigured PostgreSQL environment.
- Overview
- PostgreSQL configuration and data files
- Logging in to PostgreSQL
- Using PostgreSQL administrator
- Installed PostgreSQL modules
- Using DTrace in PostgreSQL instance
- Configuring a PostgreSQL cluster
- Metadata keys
- Version 16.x.x
- Version 15.x.x
- Version 14.x.x
- Version 13.x.x
Overview
The PostgreSQL instance is configured with the following user accounts:
- root
- admin
- postgres
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
- pg_buffercache
- pg_stat_statements
- auto_explain
- pgstattuple
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
- Base image is base-64-lts-16.4.1 (2016Q4)
- PostgreSQL Server version is 9.6.1
- PostgreSQL Admin Pack, Monitoring are now in postgresql96-contrib package
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
- Base image is base-64-lts-15.4.1 (2015Q4)
- PostgreSQL Server version is 9.4.6
- PostgreSQL Admin Pack version is 9.4.6
- PostgreSQL Monitoring version is 9.4.6
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
- Base image is base-64-15.1.1 (2015Q1)
- PostgreSQL Server version is 9.3.6
- PostgreSQL Admin Pack version is 9.3.6
- PostgreSQL Monitoring version is 9.3.6
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
- Base image is base-64-lts-14.4.0 (2014Q4)
- PostgreSQL Server version is 9.3.5
- PostgreSQL Admin Pack version is 9.3.5
- PostgreSQL Monitoring version is 9.3.5
- Metadata key 'pgsql_password' can be pre-defined during provision to set postgresql password
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
- Base image is base64-14.3.0 (2014Q3)
- PostgreSQL Server version is 9.3.5
- PostgreSQL Admin Pack version is 9.3.5
- PostgreSQL Monitoring version is 9.3.5
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
- Prevent misconfiguration of /var/pgsql/data/pg_hba.conf when a VM has no private IP available.
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
- Base image is base64-14.2.0 (2014Q2)
- PostgreSQL Server version is 9.3.4
- PostgreSQL Admin Pack version is 9.3.4
- PostgreSQL Monitoring version is 9.3.4
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
- Base image is base64-14.1.0 (2014Q1)
- root, admin metadata passwords removed (accounts are ssh key only)
- /etc/product file includes description of image
- PostgreSQL Server version is 9.3.4
- PostgreSQL Admin Pack version is 9.3.4
- PostgreSQL Monitoring version is 9.3.4
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
- Initial release
- Base image is base64-13.3.1 (2013Q3)
- PostgreSQL Server version is 9.3.0
- PostgreSQL Admin Pack version is 9.3.0
- PostgreSQL Monitoring version is 9.3.0
- PostgreSQL Server tuned by default
- Includes PostgreSQL loadable modules adminpack, pg_buffercache, pg_stat_statements, and auto_explain
- Includes DTrace scripts from 'dtracetools' package
- Npm Manta installed by default
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