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

Usage: pg_export [options]
    -d, --database DATABASE          [Required] Name of the database to export
    -k, --keep [KEEP]                [Optional] Number of dump files to keep on FTP (default: 10)
    -t, --timestamped                [Optional] Enables log messages with timestamps
    -i, --interactive                Interactive, command line mode, for restoring dumps into databases
    -h, --help                       Show this message

Setting can be verified by running following commands:
    -c, --configuration              Prints the configuration
    -f, --ftp                        Tries connecting to FTP to verify the connection

How to start

Step 1. Prepare FTP account and put configuration into env variables. Dumps will be exported into that location.

# /etc/environment
BACKUP_FTP_HOST="yourftp.example.com"
BACKUP_FTP_USER="user"
BACKUP_FTP_PASSWORD="password"

Step 2. Put dump encryption key into env variable (exactly 16 characters). Dumps will be SSL(AES-128-CBC) encrypted using that key.

# /etc/environment
DUMP_ENCRYPTION_KEY="1234567890abcdef"

Variables cannot include # sign, more info.

Step 3. Configure how many dumps should be kept in FTP (optional).

# /etc/environment
KEEP_DUMPS=5   # default: 10

Step 4. Print the configuration to verify whether env variables has been loaded.

$ pg_export --configuration
=> {:database=>"undefined", :keep_dumps=>10, :dump_encryption_key=>"k4***", :ftp_host=>"yourftp.example.com", 
  :ftp_user=>"user", :ftp_password=>"pass***", :logger_format=>:plain}

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

$ pg_export --ftp
=> Connect to yourftp.example.com

Step 6. Perform database export.

$ pg_export -d your_database
=> Create Dump Tempfile (1.36MB)
   Create Encrypted Dump Tempfile (1.34MB)
   Connect to yourftp.example.com
   Export Encrypted Dump Tempfile (1.34MB) your_database_20161020_125747 to yourftp.example.com
   Close FTP

How to restore a dump?

Go to interactive mode and follow the instructions:

pg_export -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.