PGCLI: Pretty Good Command Line Interface

PGC is a package and update manager, similar to yum or apt-get, specifically for PostgreSQL and related community projects. PGC enables you to easily find and install packages, configure multiple versions of PostgreSQL, update existing applications, and control services (like Postgres or PostgreSQL Studio) by starting and stopping from the command line. PGC works across Linux, Windows, and OSX with the exact same command line interface and integrates with native OS controls for running services.

1.) Review the help file

Get into the product home directory at the command line. Windows users don't prefix the pgc command with ./ as shown in the below examples.

Now run the help command to see that things are as expected


   $ ./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

2.) Demonstrate the pgc command line environment

The info command lists information about the OS or a component
   $ ./pgc info

                  PGC: v2.8.1  /Users/pguser/bigsql
          User & Host: pguser  machine-name 127.0.0.1
                   Os: Mac OS X 10.9.5
             Hardware: 16 GB, 2 x Intel Core i5-3210M @ 2.50GHz
      Last Update UTC: None


   $ ./pgc info pg95

              Project: pg (http://postgresql.org)
            Component: Component: pg95 9.5.3-2
         Release Date: 2016-05-12
                Stage: prod
                  Doc: http://www.postgresql.org/docs/9.5/
             RelNotes: http://www.postgresql.org/docs/9.5/static/release-9-5.html
            IsCurrent: 1
          IsInstalled: 1
                 port: 5432
              datadir: /Users/pguser/bigsql/data/pg95
               logdir: /Users/pguser/bigsql/data/logs/pg95
               status: Running
            data size: 7064 kB
             up since: 2 minutes and 53 seconds
          connections: 1/100

The list command displays installed and available components. It confirms that only the core
Postgres server component, in this case pg95, is installed and enabled by default.

   $ ./pgc list
        Category     | Component      | Version    | Status         | Port     | Updates
        PostgreSQL     pg92                  9.2.17-5   NotInstalled
        PostgreSQL     pg93                  9.3.13-5   NotInstalled
        PostgreSQL     pg94                  9.4.8-5    NotInstalled
        PostgreSQL     pg95                  9.5.3-5    Installed      5432
        Extensions     cassandra_fdw3-pg95   3.0.0-1    NotInstalled
        Extensions     hadoop_fdw2-pg95      2.1.0-1    NotInstalled
        Extensions     oracle_fdw1-pg95      1.4.0-1    NotInstalled
        Extensions     orafce3-pg95          3.3.0-1    NotInstalled
        Extensions     pgtsql9-pg95          9.5-1      NotInstalled
        Extensions     pljava15-pg95         1.5.0-1    NotInstalled
        Extensions     plv814-pg95           1.4.8-1    NotInstalled
        Extensions     postgis22-pg95        2.2.2-2    NotInstalled
        Servers        bam2                  1.5.0      NotInstalled
        Servers        cassandra30           3.0.6      NotInstalled
        Servers        pgstudio2             2.0.1-2    NotInstalled
        Servers        tomcat8               8.0.35     NotInstalled
        Applications   backrest              1.02       NotInstalled
        Applications   birt                  4.5.0      NotInstalled
        Applications   ora2pg                17.4       NotInstalled
        Applications   pgadmin3              1.22.1     NotInstalled
        Applications   pgbadger              8.1        NotInstalled
        Frameworks     java8                 8u92       NotInstalled

3.) Start and stop PostgreSQL



The status command confirms whether server components are listening on their assigned ports.
   $ ./pgc status

       pg95 stopped on port 5432

The start command will start up postgres
   $ ./pgc start

       pg95 starting on port 5432

Running the status command displays information about installed server components.
   $ ./pgc status

       pg95 running on port 5432
Now lets use the stop command to shutdown the server components.
   $ ./pgc stop

       pg95 stopping

4.) Install additional components

Use the install command to provision additional components such as pgstudio2.
Notice that the java8 dependency of pgstudio2 also installs for you.
   $ ./pgc install pgstudio2

          ['java8', 'pgstudio2']

        Get:1 http://s3.amazonaws.com/pgcentral java8-8u92-osx64
           Unpacking java8-8u92-osx64.tar.bz2

        Get:2 http://s3.amazonaws.com/pgcentral pgstudio2-2.0.1-2
           Unpacking pgstudio2-2.0.1-2.tar.bz2


   $ ./pgc list

        Category     | Component           | Version  | Status       | Port     
        PostgreSQL     pg92                  9.2.19-1   NotInstalled
        PostgreSQL     pg93                  9.3.15-1   NotInstalled
        PostgreSQL     pg94                  9.4.10-1   NotInstalled
        PostgreSQL     pg95                  9.5.5-1    Installed      5432
        Extensions     cassandra_fdw3-pg95   3.0.0-1    NotInstalled
        Extensions     hadoop_fdw2-pg95      2.1.0-1    NotInstalled
        Extensions     oracle_fdw1-pg95      1.4.0-1    NotInstalled
        Extensions     orafce3-pg95          3.3.0-1    NotInstalled
        Extensions     pgtsql9-pg95          9.5-1      NotInstalled
        Extensions     pljava15-pg95         1.5.0-1    NotInstalled
        Extensions     plv814-pg95           1.4.8-1    NotInstalled
        Extensions     postgis22-pg95        2.2.2-2    NotInstalled
        Servers        bam2                  1.5.0      NotInstalled
        Servers        pgstudio2             2.0.1-2    Installed      8765
        Applications   backrest              1.12       NotInstalled
        Applications   ora2pg                18.0       NotInstalled
        Applications   pgadmin3              1.23.1     NotInstalled
        Applications   pgbadger              9.1        NotInstalled
        Frameworks     java8                 8u121      Installed
We now run the status command to confirm that both the Postgres & pgStudio servers are installed.
   $ ./pgc status

        pg95 stopped on port 5432
        pgstudio2 stopped on port 8765

The start command starts up all servers that are enabled.
   $ ./pgc start

       pg95 starting on port 5432
       pgstudio2 starting on port 8765

Running the status command displays information about installed server components.
   $ ./pgc status

       pg95 running on port 5432
       pgstudio2 running on port 8765

5.) Installing PostgreSQL Extensions

Postgres by BigSQL includes additional useful extensions that can easily be installed to add additional functionality, procedural languages, or foreign data wrappers. Use the list command to see the extensions available for the Postgres versions installed:


  ./pgc list --extensions pg95
  Category   | Component          | Version  | Status
  Extensions   cassandra_fdw3-pg95   3.0.0-1   NotInstalled
  Extensions   hadoop_fdw2-pg95      2.1.0-1   NotInstalled
  Extensions   oracle_fdw1-pg95      1.4.0-1   NotInstalled
  Extensions   orafce3-pg95          3.3.0-1   NotInstalled
  Extensions   pgtsql9-pg95          9.5-1     NotInstalled
  Extensions   pljava15-pg95         1.5.0-1   NotInstalled
  Extensions   plv814-pg95           1.4.8-1   NotInstalled
  Extensions   postgis22-pg95        2.2.2-2   NotInstalled

And you use the install command to install extensions just like other components:

  ./pgc install orafce3-pg95
 ['orafce3-pg95']
Get:1 http://s3.amazonaws.com/pgcentral orafce3-pg95-3.3.0-1-osx64
 Unpacking orafce3-pg95-3.3.0-1-osx64.tar.bz2

After installing, enable the extension in the specific database you want to use it in via psql:


  postgres=# CREATE EXTENSION orafce;

6.) Using on Windows

When using PGC in a Windows command prompt, no ./ prefix like in the linux and OSX examples above. So a typical command would look like:
   C:\ cd PostgreSQL
   pgc info
	          PGC: v3.1.1  C:\PostgreSQL
          User & Host: pguser  \\pguser-PC\pguser-PC
                   Os: Microsoft Windows 7 Ultimate, SP 1
             Hardware: 0.5 GB, 1 x Intel Core i5-3210M @ 2.50GHz
             Repo URL: http://s3.amazonaws.com/pgcentral
      Last Update UTC: None

7.) Configure PostgreSQL as a background service

Use the configcommand to set PostgreSQL to automatically start and stop as an operating system service (on) or later remove the service (off).
   pgc config pg95 --autostart=on