Open pg:push/pull feedback
Closed this issue · 41 comments
This issue is for collecting feedback on pg:push
and pg:pull
. These two commands will soon be promted from pg-extras into the Heroku Toolbelt proper. When this happens the existing db:push
and db:pull
commands will become deprecated, and later removed to a plugin. The documentation here is also serves as a draft for the eventual devcenter article.
background
For extraordinarily simple and tiny databases, the old db:*
commands are alright, but they are unsuitable for any database that uses real postgres features—or for any database bigger than "tiny". With more and more people having trouble with those commands, we needed a modern replacement.
Unlike the db:*
commands which read all your data into ruby and tries to massage data between sqlite and postgres if needed, the new pg:push/pull
commands the same binary pg_dump
and pg_restore
commands that are standard for moving postgres data around. The downside is that this replacement does not work with sqlite. The upsides however are that it actually works, and is often very fast due to the native compression pg_dump
does.
installing
If you are new to the pg-extras plugin, please see the installation instructionsin the readme. Please note that currently, pg-extras requires a Heroku Toolbelt version ≥2.40.0.
You must also have a working local postgres installation.
usage
pg:pull
pg:pull
can be used to pull remote data from a Heroku Postgres database to a database on your local machine. The command looks like this:
$ heroku pg:pull DATABASE mylocaldb --app sushi
This command will create a new local database named "mylocaldb" and then pull data from database at DATABASE_URL
from the app "sushi". In order to prevent accidental data overwrites and loss, the local database must not exist. You will be prompted to drop an already existing local database before proceeding.
Note: like all pg:*
comamnds you can use the shorthand identifiers here, so to pull data from HEROKU_POSTGRESQL_RED
on the app "sushi" you could do heroku pg:pull sushi::RED mylocaldb
.
pg:push
Like pull but in reverse, pg:push
will push data from a local database into a remote Heroku Postgres database. The command looks like this:
$ heroku pg:push mylocaldb DATABASE --app sushi
This command will take the local database "mylocaldb" and push it to the database at DATABASE_URL
on the app "sushi". In order to prevent accidental data overwrites and loss, the remote database must be empty. You will be prompted to pg:reset
an already a remote database that is not empty.
feedback
Please use this issue for any feedback on this upcoming feature or documentation.
I assume the "-t TABLE_NAME" option won't be available to pull/push a single table since these commands only do full DB drop/creates.
One of my use cases is pulling down a table from production to dev, massaging the data, then pushing back to production. It's not a common use case, and probably not best practice, but I've found it useful a number of times. Also, I like pulling only certain tables down from PROD to DEV so, for example, i'm not pulling down a 200k+ user table to my local dev machine, but I can pull down an updated 1000 record feature/post/attribute table.
I assume the "-t TABLE_NAME" option won't be available to pull/push a single table since these commands only do full DB drop/creates.
We will eventually want to allow for that as well, perhaps even before promoting this to the toolbelt proper. So while we have thought about it, and punted on it for now, your feedback is considered a +1 on that feature. So thanks!
One of my use cases is pulling down a table from production to dev, massaging the data, then pushing back to production. It's not a common use case, and probably not best practice, but I've found it useful a number of times. Also, I like pulling only certain tables down from PROD to DEV so, for example, i'm not pulling down a 200k+ user table to my local dev machine, but I can pull down an updated 1000 record feature/post/attribute table.
In addition you could use dblink or, in postgres 9.3, the postgres foreign data wrapper, to SELECT and INSERT remotely directly. Just throwing out alternatives here.
I might add options like table name for pulling, but I'm somewhat uncomfortable adding partial pushing. I've had to answer so many support tickets over the years, that I'd rather pushing be completely safe.
That said, for your use case, have you considered using pg:psql
and immediately starting a transaction? That way you can play with your data safely.
That said, for your use case, have you considered using pg:psql and immediately starting a transaction? That way you can play with your data safely.
a great point. It goes like this:
BEGIN
UPDATE your_table ....
-- inspect your data with SELECTs, counts and such
-- everything looks good?
COMMIT
(or ROLLBACK
if you messed up)
Got the following error:
heroku pg:push local_db HEROKU_DB
pg_dump: server version: 9.2.4; pg_dump version: 9.1.5
pg_dump: aborting because of server version mismatch
pg_dump: *** aborted because of error
pg_restore: [archiver] input file is too short (read 0, expected 5)
Your local postgres instillation is 9.1 but you're trying to restore to a 9.2 database. You're going to have to install 9.2 locally.
Thanks for pasting the error. It would probably be good to catch that and give a hint of how to solve it.
The other—somewhat extreme—solution would be to build statically linked pg_dump and pg_restore for every platform, but I'm saving that as a last resort if this version mismatch is a widespread problem. I've had no troubles myself by having 9.2 locally, and with 9.2 or 9.1 remote.
Actually @fedesoria if you're doing a push, it should be dumping your local db. Your local server is 9.2 but your pg_dump binary is 9.1, there might be something weird with your $PATH.
What do you get for these commands?
~ ➤ pg_config --bindir --version
/usr/local/Cellar/postgresql/9.2.4/bin
PostgreSQL 9.2.4
~ ➤ pg_dump --version
pg_dump (PostgreSQL) 9.2.4
~ ➤ which pg_dump
/usr/local/bin/pg_dump
~ ➤ psql
Time: 9.579 ms
psql (9.3devel, server 9.2.4)
Type "help" for help.
will=# \q
~ ➤
⇒ pg_config --bindir --version
/usr/bin
PostgreSQL 9.1.5
⇒ pg_dump --version
pg_dump (PostgreSQL) 9.1.5
⇒ which pg_dump
/usr/bin/pg_dump
⇒ psql
psql (9.1.5, server 9.2.4)
WARNING: psql version 9.1, server version 9.2.
Some psql features might not work.
Type "help" for help.
I'm using the latest Postgres.app
So, this method requires a Heroku Toolbelt version ≥2.40.0, but I cannot update my Heroku Toolbelt.
$ heroku version
heroku-toolbelt/2.39.5 (x86_64-linux) ruby/1.9.3
$ heroku update
! To update this version of the Heroku client please use
! `apt-get install heroku-toolbelt'
$ sudo apt-get install heroku-toolbelt
Reading package lists... Done
Building dependency tree
Reading state information... Done
heroku-toolbelt is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 20 not upgraded.
I use CrunchBang (Debian-based Linux).
Thank you
It would be real nice if pull could write to a pre-existing database, and support the traditional --confirm
mechanism.
I got the following error:
! createdb: could not connect to database postgres: could not connect to server: No such file or directory
! Is the server running locally and accepting
! connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
!
! Unable to create new local database. Ensure your local Postgres is working and try again.
My local Postgres server is running on localhost:5432. Not sure if there is a way to specify this?
Seems to work great on both a super tiny db and a somewhat larger (400MB) db.
Much better than taps.
🌟 🌈
@ipajoneskk do you have the repository installed? I use it and I have:
heroku-toolbelt/2.40.1 (x86_64-linux) ruby/1.9.3
Yeah, same here:
maciek@gamera:~$ heroku --version
heroku-toolbelt/2.40.1 (x86_64-linux) ruby/1.9.3
The packages were stuck on 2.39.5 for a while, but the issue was fixed a few days ago.
I should have the repository installed. I have used "sudo apt-get install" before and it was working.
On Fri, Sep 6, 2013 at 3:55 PM, ipajoneskk notifications@github.com wrote:
I should have the repository installed. I have used "sudo apt-get install"
before and it was working.
Some distros (e.g. Ubuntu) like to disable third party repositories on
upgrades, so "should" may be a problem.
Also, run apt-get update; maybe you have old index files, although one
might think after the last couple of days they would have been refreshed at
least once.
@fdr Thanks, it works now
$ heroku --version
heroku-toolbelt/2.40.1 (x86_64-linux) ruby/1.9.3
@harryglaser That's the default port for Postgres, and the default error message you get when you run createdb
and Postgres isn't running. Are you sure it's running?
@catsby Yep, it is.
wit-3:secure-springs-5928 harry$ psql -h localhost -d PeriscopeSpine_development -c "select 1;"
?column?
----------
1
(1 row)
wit-3:secure-springs-5928 harry$ heroku pg:pull DATABASE_URL testdb -a secure-springs-5928
! createdb: could not connect to database postgres: could not connect to server: No such file or directory
! Is the server running locally and accepting
! connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
!
! Unable to create new local database. Ensure your local Postgres is working and try again.
wit-3:secure-springs-5928 harry$
Looks to me like a mismatch between the libpq
and command line utilities in $PATH
and the ones one is expecting to use (notably, this patch assumes the default connection settings will work).
One way to perhaps work around this is to use something like postgres://localhost:port/dbname
explicitly rather than relying on libpq
's default (the unix socket /var/pgsql_socket/.s.PGSQL.5432
in your case) but a better error message might be nice.
@fdr Personally I'd settle for an option to specify the connection settings.
I see, it looks like the current implementation hard-codes use of the default database definition; maybe that is acceptable to change. Perhaps it can pull something similar to libpq
's handling of matter. My last search suggests that it is libpq
that handles whether the string is passed on one of three ways:
- short form e.g.
psql foo
connecting tofoo
- connection string e.g.
psql dbname=foo
- URI e.g.
psql postgres:///foo
Supporting these additional forms would result in no change for the current shorthand case but allow specification of other connection strings or URIs.
Annoyingly, if memory serves, pg_dump
and pg_restore
can't accept connection strings to just let libpq
think about it, so instead it requires laborious handling of each option, so supporting each part of a connection string will have to happen blow by blow...
Hi,
Just wanted to share what was my fix.
- Deleted
/usr/bin/pg_dump
- Ran
sudo ln -s /Applications/Postgres.app/Contents/MacOS/bin/pg_dump /usr/bin/pg_dump
Thanks for the help guys.
+1 for an option to use the last capture for pg:pull @neilmiddleton
just a shortcut for curl & pg_restore that doesn't hit the production DB for every developer that wants to do a pull
Thanks for the feedback everyone. pg:push and pull have been promoted to the toolbelt proper as of version 3.0.0 https://devcenter.heroku.com/changelog-items/333
When this happens the existing db:push and db:pull commands will become deprecated, and later removed to a plugin.
@will have you released this plugin already? Removing of db:push
/db:pull
has broken my workflow.
@denis Yes it's over at https://github.com/heroku/heroku-legacy-taps
I find the removal of db:push
horrible. I'm fine with the moving to a plugin but deprovisioning the taps servers sounds like a huge regression for paying Heroku customers (like me).
I switch machines often and have grown fond of this feature. From cursory searches on the web, there are lots of people who use this feature. I imagine there's a lot of demand for it. Am I wrong?
@GantMan Sorry, it's very likely not come back. Taps is not accurate. It is slow. It encourages an insidious practice of using a totally different database system (like SQLite) with a totally different type system, among other things, to model the production behavior. Consider all the tickets @will cross-referenced above. There's also a litany of support issues stemming from the same. Taps is popular (for lack of alternative, at the least) but not really okay.
It was not churned for no reason, even if the replacement is not superior to the old in every dimension for everyone.
If you would be so kind, it'd be nice to know why you cannot use the new toolchain.
I appreciate your quick reply. I know my need doesn't apply to everyone, and I completely understand Heroku has always been clear on their stance against using SQLite in dev, and then PG in prod.
The reason you asked for:
I can no longer easily fix a Production bug on a dev machine that doesn't have PG.
(Which was the purpose of the feature for a lot of people, so I believe)
I understand making two different systems work is costly in dev. It's just that losing a powerful feature I've appreciated is hard.
pg: commands assume your Heroku app is using a Postgres database. My Heroku app uses a ClearDB MySQL database, and I loved that the db: commands could deal with that. This change breaks so much good and turns it into bad.
On Wed, Oct 16, 2013 at 2:29 AM, Stephen Hunter notifications@github.comwrote:
pg: commands assume your Heroku app is using a Postgres database. My
Heroku app uses a ClearDB MySQL database, and I loved that the db: commands
could deal with that. This change breaks so much good and turns it into bad.That's a very interesting use case.
It's probably a bad idea for MySQL, too, though.
Taps is, again, slow and buggy, and nobody is going to great lengths to fix
it. Someone else is free to maintain a fork and make it thrive and maybe
then this idea can be revisited, but to date nobody at Heroku has wanted to
take up the sword to make Taps accurate and fast.
And so, here we are.
I am sorry you are among the disappointed.
Hey guys - having what is probably a simple problem, but am unsure if this is an issue with pg:pull, or my own (rather vanilla) configuration. When I run:
heroku pg:pull ss-api-prod::RED ss_api_local
I keep running into createdb: could not connect to database template1: FATAL: password authentication failed
I'm using the same password (password) that rails uses to connect, successfully, etc.
Something dumb here i'm missing?
I keep running into createdb: could not connect to database template1: FATAL: password authentication failed
This means that psql can't connect to your local postgres instance with the information provided. How are you setting your local user/password? Try this instead:
PGUSER=some_user PGPASSWORD=some_password heroku pg:pull [...]
Yes! that was the key - thanks, didn't realize it was pulling those env vars.
Hi guys,
Still getting some errors but they are being ignored, just a heads up:
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2629; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
pg_restore: creating EXTENSION hstore
pg_restore: creating COMMENT EXTENSION hstore
pg_restore: [archiver (db)] Error from TOC entry 2630; 0 0 COMMENT EXTENSION hstore
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension hstore
Command was: COMMENT ON EXTENSION hstore IS 'data type for storing sets of (key, value) pairs';
Is the ability to exclude tables still planned? We have one table that is enormous (millions of rows) and it makes pulling down a copy of our db very time-consuming.
pg:pull works like a charm on my Mac, but I can't get it to work on Ubuntu. I get a variety of errors about roles, databases not existing, etc. from pg_restore. I can't figure out how to get my local postgres to be configured the way pg:pull clearly wants it to be.
pg:pull works like a charm on my Mac, but I can't get it to work on Ubuntu. I get a variety of errors about roles, databases not existing, etc. from pg_restore. I can't figure out how to get my local postgres to be configured the way pg:pull clearly wants it to be.
My guess is you cannot login by default on Ubuntu, e.g. just "psql" without credentials will log in. In a nutshell, in development it is helpful to create a Postgres user and database with the same name as your unix user to have easy superuser login, and I detail those steps below.
First, confirm this may be a problem:
$ psql
psql: FATAL: role "fdr" does not exist
Here's what I do to ensure I can log in to a database by-default on a development machine:
$ sudo -u postgres createdb `whoami`
<no output>
# Makes a superuser account via "-s"
$ sudo -u postgres createuser -s `whoami`
<no output>
Then, try confirming that you can get a database prompt without any fuss:
$ psql
psql (9.3.4)
Type "help" for help.
fdr=#
It also means you can use some of the command-line wrappers for CREATE DATABASE
and such very easily:
$ createdb myapp
$ psql myapp
$ dropdb myapp
As for controlling the destination of pg:pull
, one can use Postgres-standard environment variables like PGDATABASE
(complete list).