PgExport

CLI for creating and exporting PostgreSQL dumps to FTP.

Can be used for backups or synchronizing databases between production and development environments.

Example:

pg_export --database database_name --keep 5

Above command will perform database dump, encrypt it, upload it to FTP and remove old dumps from FTP, keeping newest 5.

FTP connection params and encryption key are configured by env variables.

Features:

uses shell command pg_dump and pg_restore
encrypts dumps by OpenSSL AES-128-CBC
configurable through env variables
uses ruby tempfiles, so local dumps are garbage collected automatically
easy restoring dumps through interactive mode

Dependencies

Ruby >= 2.4 (works with Ruby 3.0)
$ pg_dump
$ pg_restore

Installation

Add this line to your application's Gemfile:

gem 'pg_export'

And then execute:

$ bundle

Or install it yourself as:

$ gem install pg_export

CLI

$ pg_export -h

SYNOPSIS
  pg_export DATABASE [OPTION..]
  pg_export --interactive DATABASE [OPTION..]

EXIT VALUES
  0 - Success
  1 - Error

ARGUMENTS
  DATABASE - database name to export (when default mode)
           - phrase to filter database dumps by (when interactive mode)

OPTIONS
  -g, --gateway GATEWAY      Allowed values: ftp, ssh. Default: ftp. Credentials need to be set via ENVs
  -U, --user USER            Gateway (ftp or ssh) user
  -H, --host HOST            Gateway (ftp or ssh) host
  -P, --password PASSWORD    Gateway (ftp or ssh) password
  -d, --database DATABASE    In plain mode: database name to export;
                             In interactive mode: phrase to filter by
  -e, --encryption_key KEY   Dumps will be SSL encrypted using this key. Should have exactly 16 characters
  -a, --algorithm ALGORITHM  Encryption cipher algorithm (default: AES-128-CBC);
                             For available option see `$ openssl list -cipher-algorithms`
  -k, --keep KEEP            Number of dump files to keep on FTP (default: 10)
  -s, --ssh                  Same as "--gateway ssh". When set, the --gateway option is ignored
  -f, --ftp                  Same as "--gateway ftp". When set, the --gateway option is ignored
  -t, --timestamped          Prepends log messages with timestamps
  -m, --muted                Mutes log messages. When set, the -t option is ignored. Prints message only on error
  -i, --interactive          Interactive mode, for importing dumps. Whan set, the -t and -m options are ignored
  -w, --welcome              Try connecting to the gateway (FTP or SSH) to verify the connection and exit
  -c, --configuration        Print the configuration and exit
  -v, --version              Print version
  -h, --help                 Print this message and exit

ENV
  Each of the above options can be also set using enviromental variables.
  Use full option names prepending with PG_EXPORT_ phrase.
  Command line options takes precedence over the ENVs.

  Eg. below two commands are equivalent:
    pg_export -s -U user -H host database_name -k 10

  Is equivalent to:
    export PG_EXPORT_GATEWAY_USER=user
    export PG_EXPORT_GATEWAY_HOST=host
    export PG_EXPORT_KEEP=20
    pg_export -s database_name -k 10

How to start

Step 1. Prepare ENV variables.

/* FTP storage for database dumps. */
PG_EXPORT_GATEWAY_HOST=yourftp.example.com
PG_EXPORT_GATEWAY_USER=user
PG_EXPORT_GATEWAY_PASSWORD=password

/* Encryption key should have exactly 16 characters. */
/* Dumps will be SSL(AES-128-CBC) encrypted using this key. */
PG_EXPORT_ENCRYPTION_KEY=1234567890abcdef

Step 2. Print the configuration to verify if env variables has been loaded properly.

$ pg_export --configuration
=> encryption_key k4***
   gateway_host yourftp.example.com
   gateway_user your_gateway_user
   gateway_password
   logger_format plain
   keep_dumps 10
   ....

Step 3. Try connecting to FTP to verify the connection.

$ pg_export --gateway ftp --welcome
=> 230 User your_ftp_user logged in

Step 4. Perform database export.

$ pg_export -d your_database -k 5
=> Dump database your_database to database_name (1.36MB)
   Encrypt database_name_20181016_121314 (1.34MB)
   Connect to yourftp.example.com
   Upload database_name_20181016_121314 (1.34MB) to yourftp.example.com
   Close FTP

How to restore a dump?

Run interactive mode and follow the instructions:

pg_export [-d database_name] -i

Development

After checking out the repo, run bin/setup to install dependencies. Then, run rspec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/maicher/pg_export. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.

License

The gem is available as open source under the terms of the MIT License.