heroku/heroku-pg-extras — pull request #38 by will (2013-08-24 02:05:00+00:00)
Title: pg:push and pg:pull

- version two
- original pull request via Peter Geoghegan (https://github.com/heroku/heroku-pg-extras/pull/17)

# Summary
```
~ ➤ heroku help pg:pull
Usage: heroku pg:pull

Pull from REMOTE_SOURCE_DATABASE to LOCAL_TARGET_NAME
LOCAL_TARGET_DATABASE_NAME must not already exist.

~ ➤ heroku help pg:push
Usage: heroku pg:push

Push from LOCAL_SOURCE_DATABASE to REMOTE_TARGET_DATABASE
REMOTE_TARGET_DATABASE must be empty.
```

## Example pull

```
~ ➤ heroku pg:pull will::DATABASE what
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading extensions
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: finding extension members
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "bobmorales"
pg_dump: finding default expressions of table "bobmorales"
pg_dump: finding the columns and types of table "hello"
pg_dump: finding the columns and types of table "names"
pg_dump: finding the columns and types of table "sab669"
pg_dump: finding the columns and types of table "ems_in_out"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "bobmorales"
pg_dump: reading indexes for table "sab669"
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition
pg_dump: dumping contents of table bobmorales
pg_dump: dumping contents of table ems_in_out
pg_dump: dumping contents of table hello
pg_dump: dumping contents of table names
pg_dump: dumping contents of table sab669
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: creating EXTENSION btree_gist
pg_restore: creating COMMENT EXTENSION btree_gist
pg_restore: creating EXTENSION fuzzystrmatch
pg_restore: creating COMMENT EXTENSION fuzzystrmatch
pg_restore: creating EXTENSION hstore
pg_restore: creating COMMENT EXTENSION hstore
pg_restore: creating EXTENSION uuid-ossp
pg_restore: creating COMMENT EXTENSION "uuid-ossp"
pg_restore: creating FUNCTION psqlfib(integer)
pg_restore: creating TABLE bobmorales
pg_restore: creating SEQUENCE bobmorales_id_seq
pg_restore: creating SEQUENCE OWNED BY bobmorales_id_seq
pg_restore: creating TABLE ems_in_out
pg_restore: creating TABLE hello
pg_restore: creating TABLE names
pg_restore: creating TABLE sab669
pg_restore: creating DEFAULT id
pg_restore: restoring data for table "bobmorales"
pg_restore: executing SEQUENCE SET bobmorales_id_seq
pg_restore: restoring data for table "ems_in_out"
pg_restore: restoring data for table "hello"
pg_restore: restoring data for table "names"
pg_restore: restoring data for table "sab669"
pg_restore: creating CONSTRAINT bobmorales_pkey
pg_restore: creating INDEX knn_sab
pg_restore: setting owner and privileges for DATABASE deik3qfhu265n6
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for EXTENSION plpgsql
pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql
pg_restore: setting owner and privileges for EXTENSION btree_gist
pg_restore: setting owner and privileges for COMMENT EXTENSION btree_gist
pg_restore: setting owner and privileges for EXTENSION fuzzystrmatch
pg_restore: setting owner and privileges for COMMENT EXTENSION fuzzystrmatch
pg_restore: setting owner and privileges for EXTENSION hstore
pg_restore: setting owner and privileges for COMMENT EXTENSION hstore
pg_restore: setting owner and privileges for EXTENSION uuid-ossp
pg_restore: setting owner and privileges for COMMENT EXTENSION "uuid-ossp"
pg_restore: setting owner and privileges for FUNCTION psqlfib(integer)
pg_restore: setting owner and privileges for TABLE bobmorales
pg_restore: setting owner and privileges for SEQUENCE bobmorales_id_seq
pg_restore: setting owner and privileges for SEQUENCE OWNED BY bobmorales_id_seq
pg_restore: setting owner and privileges for TABLE ems_in_out
pg_restore: setting owner and privileges for TABLE hello
pg_restore: setting owner and privileges for TABLE names
pg_restore: setting owner and privileges for TABLE sab669
pg_restore: setting owner and privileges for DEFAULT id
pg_restore: setting owner and privileges for TABLE DATA bobmorales
pg_restore: setting owner and privileges for SEQUENCE SET bobmorales_id_seq
pg_restore: setting owner and privileges for TABLE DATA ems_in_out
pg_restore: setting owner and privileges for TABLE DATA hello
pg_restore: setting owner and privileges for TABLE DATA names
pg_restore: setting owner and privileges for TABLE DATA sab669
pg_restore: setting owner and privileges for CONSTRAINT bobmorales_pkey
pg_restore: setting owner and privileges for INDEX knn_sab
```

### error on pull

```
~ ➤ heroku pg:pull will::DATABASE what
createdb: database creation failed: ERROR: database "what" already exists
! Please drop the local database (`dropdb what`) and try again.
```

## Example push

```
~ ➤ heroku pg:push what will::black
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading extensions
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: finding extension members
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "bobmorales"
pg_dump: finding default expressions of table "bobmorales"
pg_dump: finding the columns and types of table "ems_in_out"
pg_dump: finding the columns and types of table "hello"
pg_dump: finding the columns and types of table "names"
pg_dump: finding the columns and types of table "sab669"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "bobmorales"
pg_dump: reading indexes for table "sab669"
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving database definition
pg_dump: dumping contents of table bobmorales
pg_dump: dumping contents of table ems_in_out
pg_dump: dumping contents of table hello
pg_dump: dumping contents of table names
pg_dump: dumping contents of table sab669
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: creating EXTENSION btree_gist
pg_restore: creating COMMENT EXTENSION btree_gist
pg_restore: creating EXTENSION fuzzystrmatch
pg_restore: creating COMMENT EXTENSION fuzzystrmatch
pg_restore: creating EXTENSION hstore
pg_restore: WARNING: => is deprecated as an operator name
DETAIL: This name may be disallowed altogether in future versions of PostgreSQL.
pg_restore: creating COMMENT EXTENSION hstore
pg_restore: creating EXTENSION uuid-ossp
pg_restore: creating COMMENT EXTENSION "uuid-ossp"
pg_restore: creating FUNCTION psqlfib(integer)
pg_restore: creating TABLE bobmorales
pg_restore: creating SEQUENCE bobmorales_id_seq
pg_restore: creating SEQUENCE OWNED BY bobmorales_id_seq
pg_restore: creating TABLE ems_in_out
pg_restore: creating TABLE hello
pg_restore: creating TABLE names
pg_restore: creating TABLE sab669
pg_restore: creating DEFAULT id
pg_restore: restoring data for table "bobmorales"
pg_restore: executing SEQUENCE SET bobmorales_id_seq
pg_restore: restoring data for table "ems_in_out"
pg_restore: restoring data for table "hello"
pg_restore: restoring data for table "names"
pg_restore: restoring data for table "sab669"
pg_restore: creating CONSTRAINT bobmorales_pkey
pg_restore: creating INDEX knn_sab
pg_restore: setting owner and privileges for DATABASE what
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for EXTENSION plpgsql
pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql
pg_restore: setting owner and privileges for EXTENSION btree_gist
pg_restore: setting owner and privileges for COMMENT EXTENSION btree_gist
pg_restore: setting owner and privileges for EXTENSION fuzzystrmatch
pg_restore: setting owner and privileges for COMMENT EXTENSION fuzzystrmatch
pg_restore: setting owner and privileges for EXTENSION hstore
pg_restore: setting owner and privileges for COMMENT EXTENSION hstore
pg_restore: setting owner and privileges for EXTENSION uuid-ossp
pg_restore: setting owner and privileges for COMMENT EXTENSION "uuid-ossp"
pg_restore: setting owner and privileges for FUNCTION psqlfib(integer)
pg_restore: setting owner and privileges for TABLE bobmorales
pg_restore: setting owner and privileges for SEQUENCE bobmorales_id_seq
pg_restore: setting owner and privileges for SEQUENCE OWNED BY bobmorales_id_seq
pg_restore: setting owner and privileges for TABLE ems_in_out
pg_restore: setting owner and privileges for TABLE hello
pg_restore: setting owner and privileges for TABLE names
pg_restore: setting owner and privileges for TABLE sab669
pg_restore: setting owner and privileges for DEFAULT id
pg_restore: setting owner and privileges for TABLE DATA bobmorales
pg_restore: setting owner and privileges for SEQUENCE SET bobmorales_id_seq
pg_restore: setting owner and privileges for TABLE DATA ems_in_out
pg_restore: setting owner and privileges for TABLE DATA hello
pg_restore: setting owner and privileges for TABLE DATA names
pg_restore: setting owner and privileges for TABLE DATA sab669
pg_restore: setting owner and privileges for CONSTRAINT bobmorales_pkey
pg_restore: setting owner and privileges for INDEX knn_sab
```
### error on push

```
~ ➤ heroku pg:push what will::black
! Remote database is not empty.
! Please create a new database, or use `heroku pg:reset`
```

## warning on mismatching extensions (no postgis locally)
```
~ ➤ heroku pg:psql will::ivory
Time: 370.953 ms
psql (9.3devel, server 9.2.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

de22gjv2q7e85i=> create extension postgis;
CREATE EXTENSION
Time: 7836.311 ms
de22gjv2q7e85i=> create table something (data text);
CREATE TABLE
Time: 150.790 ms
de22gjv2q7e85i=> insert into something values ('ok');
INSERT 0 1
Time: 78.392 ms
de22gjv2q7e85i=> \q
~ ➤ heroku pg:pull will::ivory iDONThavePOSTGIS
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading extensions
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: finding extension members
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "spatial_ref_sys"
pg_dump: finding check constraints for table "spatial_ref_sys"
pg_dump: finding the columns and types of table "geography_columns"
pg_dump: finding the columns and types of table "geometry_columns"
pg_dump: finding the columns and types of table "raster_columns"
pg_dump: finding the columns and types of table "raster_overviews"
pg_dump: finding the columns and types of table "something"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving database definition
pg_dump: dumping contents of table something
pg_dump: dumping contents of table spatial_ref_sys
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: creating EXTENSION plv8
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 183; 3079 16399 EXTENSION plv8
pg_restore: [archiver (db)] could not execute query: ERROR: could not open extension control file "/usr/local/Cellar/postgresql/9.2.4/share/postgresql/extension/plv8.control": No such file or directory
Command was: CREATE EXTENSION IF NOT EXISTS plv8 WITH SCHEMA pg_catalog;

pg_restore: creating COMMENT EXTENSION plv8
pg_restore: [archiver (db)] Error from TOC entry 3903; 0 0 COMMENT EXTENSION plv8
pg_restore: [archiver (db)] could not execute query: ERROR: extension "plv8" does not exist
Command was: COMMENT ON EXTENSION plv8 IS 'PL/JavaScript (v8) trusted procedural language';

pg_restore: creating EXTENSION postgis
pg_restore: [archiver (db)] Error from TOC entry 184; 3079 16408 EXTENSION postgis
pg_restore: [archiver (db)] could not execute query: ERROR: could not open extension control file "/usr/local/Cellar/postgresql/9.2.4/share/postgresql/extension/postgis.control": No such file or directory
Command was: CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;

pg_restore: creating COMMENT EXTENSION postgis
pg_restore: [archiver (db)] Error from TOC entry 3904; 0 0 COMMENT EXTENSION postgis
pg_restore: [archiver (db)] could not execute query: ERROR: extension "postgis" does not exist
Command was: COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions';

pg_restore: creating TABLE something
pg_restore: restoring data for table "something"
pg_restore: restoring data for table "spatial_ref_sys"
pg_restore: [archiver (db)] Error from TOC entry 3892; 0 16648 TABLE DATA spatial_ref_sys u36k1bsipqtlik
pg_restore: [archiver (db)] could not execute query: ERROR: relation "spatial_ref_sys" does not exist
Command was: COPY spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) FROM stdin;

pg_restore: creating RULE geometry_columns_delete
pg_restore: [archiver (db)] Error from TOC entry 3889; 2618 17065 RULE geometry_columns_delete u36k1bsipqtlik
pg_restore: [archiver (db)] could not execute query: ERROR: relation "geometry_columns" does not exist
Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO geometry_columns DO INSTEAD NOTHING;

pg_restore: creating RULE geometry_columns_insert
pg_restore: [archiver (db)] Error from TOC entry 3887; 2618 17063 RULE geometry_columns_insert u36k1bsipqtlik
pg_restore: [archiver (db)] could not execute query: ERROR: relation "geometry_columns" does not exist
Command was: CREATE RULE geometry_columns_insert AS ON INSERT TO geometry_columns DO INSTEAD NOTHING;

pg_restore: creating RULE geometry_columns_update
pg_restore: [archiver (db)] Error from TOC entry 3888; 2618 17064 RULE geometry_columns_update u36k1bsipqtlik
pg_restore: [archiver (db)] could not execute query: ERROR: relation "geometry_columns" does not exist
Command was: CREATE RULE geometry_columns_update AS ON UPDATE TO geometry_columns DO INSTEAD NOTHING;

pg_restore: setting owner and privileges for DATABASE de22gjv2q7e85i
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for EXTENSION plpgsql
pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql
pg_restore: setting owner and privileges for EXTENSION plv8
pg_restore: setting owner and privileges for COMMENT EXTENSION plv8
pg_restore: setting owner and privileges for EXTENSION postgis
pg_restore: setting owner and privileges for COMMENT EXTENSION postgis
pg_restore: setting owner and privileges for TABLE something
pg_restore: setting owner and privileges for TABLE DATA something
pg_restore: setting owner and privileges for TABLE DATA spatial_ref_sys
pg_restore: setting owner and privileges for RULE geometry_columns_delete
pg_restore: setting owner and privileges for RULE geometry_columns_insert
pg_restore: setting owner and privileges for RULE geometry_columns_update
WARNING: errors ignored on restore: 8
! WARNING: Extensions in newly created target database differ from existing source database.
!
! Target extensions:
! extname
! ---------
! plpgsql
! (1 row)
!
!
! Source extensions:
! extname
! ---------
! plpgsql
! plv8
! postgis
! (3 rows)
!
!
! HINT: You should review output to ensure that any errors
! ignored are acceptable - entire tables may have been missed, where a dependency
! could not be resolved. You may need to to install a postgresql-contrib package
! and retry.
```

## note on pg_dump versions:

Even though @deafbybeheading didn't believe it when I said, this actually happens to work in BOTH directions with a remote 9.1 db and local 9.2. (with local pg_dump/restore being 9.2) So I think we might get by pretty well without worrying too much about versions.