Screen Shot 2016-10-14 at 10.35.27 AM

Holy Easy PostgreSQL deployment

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:

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
Get:1 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:

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
Get:1 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
Get:1 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!

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

Please type the characters of this captcha image in the input box

Please write the answer to the math question of this captcha image in the input box

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>