Holy Easy PostgreSQL deployment!

In case you missed it, the BigSQL team released an awesome package manager for installing and configuring PostgreSQL and many related, useful components. The package manager can be found here: https://www.bigsql.org/package-manager.jsp.

Playfully named pgc, for ‘pretty good command line’, pgc is a utility similar to yum or apt-get that allows you to install, configure, update and manage Postgres related components including foreign data wrappers, stored procedure languages, connectors, devops tools, HA tools and monitoring tools. Common uses:

  • Provision Postgres (9.2 through 9.6, including multiple versions on same server)
  • Installing pgBouncer, Backrest, and other community projects
  • Scripting configurations in chef or other devops tools

PGC runs on Linux, Windows and OS X and supports the same exact cli so it is an ideal provisioning/management tool for multi OS environments.

PGC not only allows you to get and install these components but you can use pgc to update each component as new updates become available.

PGC’s syntax is very intuitive and easy to use:

Use the help command to see the possible commands:

$ ./pgc help
Usage: pgc command [component1 component2 ...]
Common commands:
help - Show this help file
info - Display OS or component information
list - Display installed & available components
status - Display status of installed server components
start - Start server components
stop - Stop server components
enable - Enable a server component
disable - Disable a server component from starting automatically
update - Retrieve new list of available components
upgrade - Upgrade installed components to newer (compatible) versions
install - Install a component
remove - Remove a component
Advanced commands:
init - Initialize a component
download - Download a component
config - Configure a component
clean - Remove component files from download cache
enable - Enable a server component
disable - Disable a server component from starting automatically

Example workflow:

1) Checking to see what components are available

The list command displays installed and available components. It confirms that the core 
Postgres 9.6 server component is installed.

$ ./pgc list Category     | Component           | Version   | Stage | ReleaseDt  | Status         | Cur? | Updates 
PostgreSQL     pg92                  9.2.18-2a           2016-09-15                    1 
PostgreSQL     pg93                  9.3.14-2a           2016-09-15                    1 
PostgreSQL     pg94                  9.4.9-2a            2016-09-15                    1 
PostgreSQL     pg95                  9.5.4-2             2016-08-18                    1 
PostgreSQL     pg96                  9.6.0-1             2016-09-29   Installed        1 
Extensions     hadoop_fdw2-pg96      2.5.0-1             2016-09-01                    1 
Extensions     oracle_fdw1-pg96      1.5.0-1             2016-09-01                    1 
Extensions     orafce3-pg96          3.3.1-1             2016-09-23                    1 
Extensions     plprofiler3-pg96      3.0-1               2016-10-08                    1 
Extensions     plv814-pg96           1.4.8-1             2016-09-01                    1 
Extensions     postgis22-pg96        2.2.2-2             2016-09-01                    1 
Extensions     postgis23-pg96        2.3.0-1             2016-10-08                    1 
Extensions     slony22-pg96          2.2.5-2             2016-09-01                    1 
Servers        bam2                  1.6.2               2016-09-15                    1 
Servers        cassandra30           3.0.8               2016-09-01                    1 
Servers        hadoop26              2.6.4               2016-02-14                    1 
Servers        hive2                 2.0.1               2016-06-16                    1 
Servers        pgbouncer17           1.7.2-1a            2016-10-08                    1 
Servers        pgha2                 2.1b                2015-12-17                    1 
Servers        pgstudio2             2.0.1-2             2016-03-23                    1 
Servers        spark16               1.6.1               2016-03-16                    1 
Servers        tomcat8               8.5.4               2016-09-01                    1 
Servers        zookeeper34           3.4.8               2016-03-30                    1 
Applications   backrest              1.08                2016-09-23                    1 
Applications   birt                  4.5.0               2016-05-12                    1 
Applications   ora2pg                17.4                2016-05-12                    1 
Applications   pgbadger              9.0                 2016-09-03                    1 
Frameworks     java8                 8u92                2016-07-01                    1 

2) Installing a component

Use the install command to provision a component

$ ./pgc install pg96 ['pg96'] 
Get:1 http://s3.amazonaws.com/pgcentral pg96-9.6.0-1-linux64 
Unpacking pg96-9.6.0-1-linux64.tar.bz2

3) Initialize PostgreSQL 9.6

./pgc init pg96
  Initializing pg96
  Superuser Password [password]: 
  Confirm Password: 
Giving current user permission to data dir Initializing Postgres DB at: -D "/software/bigsql/data/pg96" 
Using PostgreSQL Port 5432 
Password securely remembered in the file: /home/oscgadmin/.pgpass 
to load this postgres into your environment, source the env file: /software/bigsql/pg96/pg96.env 

4) Add extensions, such as FDW’s, PostGIS, Slony replication, or the NEW plProfiler 3

$ ./pgc list --extensions pg96
Category   | Component        | Version | Stage | ReleaseDt  | Status | Cur? | Updates 
Extensions   hadoop_fdw2-pg96   2.5.0-1           2016-09-01            1 
Extensions   oracle_fdw1-pg96   1.5.0-1           2016-09-01            1 
Extensions   orafce3-pg96       3.3.1-1           2016-09-23            1 
Extensions   plprofiler3-pg96   3.0-1             2016-10-08            1 
Extensions   plv814-pg96        1.4.8-1           2016-09-01            1 
Extensions   postgis22-pg96     2.2.2-2           2016-09-01            1 
Extensions   postgis23-pg96     2.3.0-1           2016-10-08            1 
Extensions   slony22-pg96       2.2.5-2           2016-09-01            1 

$ ./pgc install plprofiler3-pg96 ['plprofiler3-pg96'] 
Get:1 http://s3.amazonaws.com/pgcentral plprofiler3-pg96-3.0-1-linux64 
Unpacking plprofiler3-pg96-3.0-1-linux64.tar.bz2 

5) Install and launch a GUI management and monitoring console – BigSQL Manager:

$ ./pgc install bam2 ['bam2'] 
Get:1 http://s3.amazonaws.com/pgcentral bam2-1.6.2 
Unpacking bam2-1.6.2.tar.bz2 oscgadmin@ubuntu:~/Downloads/bigsql
$ ./pgc start bam2 
bam2 starting on port 8050 
bam2 started on port 8050. 

BigSQL Manager

As you can see, pgc by BigSQL is an easy to use provisioning tool for Postgres and its related components. Anyone looking to use Postgres in their DevOps environment would benefit from using pgc so take a look at it today! https://www.bigsql.org/package-manager.jsp