Push database to heroku: how to use heroku pg:push

Question

I want to push my local postgresql database to heroku, using heroku pg:push command. The command looks like this: heroku pg:push mylocaldb DATABASE --app sushi according to the heroku document: https://devcenter.heroku.com/articles/heroku-postgresql.

Here is my local database info:

Name: mysitedb
User: bill
Password: bill

The DATABASE_URL environment variable in my machine is set to: postgres://bill:bill@localhost/mysitedb.

My app's name is secure-gorge-4090. I tried heroku pg:push mysitedb DATABASE --app secure-gorge-4090. The output was:

 !    Remote database is not empty.
 !    Please create a new database, or use `heroku pg:reset`

I was surprised that I have put nothing into my DATABASE. But I still ran heroku pg:reset DATABASE to reset my DATABASE. After that, I tried heroku pg:push mysitedb DATABASE --app secure-gorge-4090 again but the output was still the same.

I tried heroku pg:push postgres://bill:bill@localhost:8000/mysitedb DATABASE --app secure-gorge-4090. The output was:

!    LOCAL_SOURCE_DATABASE is not a valid database name

I don't know how to use this command to move my local database to heroku. I need your help. Thanks!

Matelda

Answer 1

Are you actually typing in the token DATABASE in your commands, or is that a placeholder you're using for this question? From the docs you linked to:

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

Usage of the PGUSER and PGPASSWORD for your local database is also supported
for pg:push, just like for the pg:pull commands.

When you do heroku config -a secure-gorge-4090, you should see an entry for HEROKU_POSTGRESQL_[SOME COLOR NAME]. Make sure you're using whatever that token is instead of DATABASE in your commands.

Since you have a username and password on your local database, you also need to do the part mentioned about PGUSER and PGPASSWORD. Here's the example from the pg:pull docs:

$ PGUSER=postgres PGPASSWORD=password heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

So you should do something like:

$ PGUSER=bill PGPASSWORD=bill heroku pg:push mysitedb HEROKU_POSTGRESQL_[SOME COLOR] -a secure-gorge-4090

Artemisa

Thanks for paying attention to this old unsolved question! I just typed DATABASE in my commands. In fact, DATABASE is already set as my default database -- HEROKU-POSTGRESQL-VIOLET. So the commands heroku pg:push mysitedb DATABASE --app secure-gorge-4090 and heroku pg:push mysitedb HEROKU-POSTGRESQL-VIOLET --app secure-gorge-4090 should have the same effect. Following your advice, I created a new database named HEROKU_POSTGRESQL_COBALT and tried PGUSER=bill PGPASSWORD=bill heroku pg:push mysitedb HEROKU_POSTGRESQL_COBALT --app secure-gorge-4090, but the same error occured. – Matelda

Which error? You mention a "Remote database is not empty" error and a "LOCAL_SOURCE_DATABASE is not a valid database name" error. – Artemisa

Did you run a pg:reset on HEROKU_POSTGRESQL_COBALT? – Refurbish

thank you - when I run "heroku config:get -a secure-gorge-4090" i get a missing arguments exception --of course i am using my own app name – Perm

@BKSpurgeon try heroku config -a secure-gorge-4090, I think heroku changed the command since I wrote this? I've updated my answer as well. – Artemisa

@Artemisa I get 'PGUSER' is not recognized as an internal or external command, operable program or batch file. please help – Everlasting

are PGUSER and PGPASSWORD path variables – Stribling

Answer 2

I know this is a old discussion but I had the exact same problem. Though it's not quite as convenient, I managed to achieve this via pg:backups instead.

This is detailed pretty nicely on the heroku support site

Start by installing the free pgbackups addon:

heroku addons:add pgbackups

Then backup the database using your local pg_dump utility (included in PostGreSQL distro)

pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dmp

Then put that dump file somewhere URL-addressible (e.g. Dropbox) and run the heroku import(make sure it's double quotes for Windows):

heroku pg:backups:restore 'https://dropbox.com/dYAKjgzSTNVp4jzE/mydb.dmp' DATABASE_URL

Assemblyman

Two things to note: WARNING: heroku addons:add has been deprecated. Please use heroku addons:create instead. ! Couldn't find either the add-on service or the add-on plan of "pgbackups". – Brownstone

Answer 3

You need the following command

PGUSER=root PGPWD=root heroku pg:push (local database name) DATABASE_URL --app heroku (app name)

make sure that you have entered correct postgres username and password

Romanticism

I get 'PGUSER' is not recognized as an internal or external command, operable program or batch file. – Stribling

Answer 4

I'm a lazy programmer and efficient so this is much more easier rather than paying for AWS backup store them in excel sheets. This save cost and not use PUSH: PULL which is not efficient.

Using CMD as ADMIN to insert Excel data to Heroku Postgres Database.

Follow INSTRUCTION

1. OPEN CMD AS ADMIN

2. heroku pg:sql postgresql-rugged-08088 --app sample

3. CREATE TABLE SERIAL_T (  id SERIAL , SERIAL VARCHAR(50),  USE INT,    DEVICES TEXT[], PRINTED BOOLEAN,  PRIMARY KEY (id))

4. \COPY SERIAL_T (SERIAL, USE, DEVICES, PRINTED) FROM 'C:\Users\PATH\EXCEL-03-27-2021.csv' DELIMITER ','CSV HEADER;

Bellabelladonna

Answer 5

What worked for me without any issues was

pg_dump -f database_output_name --no-owner --no-acl -U user_name name_of_your_local_database

• database_output_name name of the output file you can rename it whatever backup, database ...etc
• user_name: the postgres sql user_name mainly postgres
• name_of_your_local_database: is the name of your database: mydb or whatever the name you gave if you forgot it you can check in PgAdmin
• pg_dump I have export it to global path that is why I am using it like this, other ways you can call it with the absolute path ie: C:\"Program Files"\PostgreSQL\14\bin\pg_dump

the grenerated ourput file shoule look something like this

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.1
-- Dumped by pg_dump version 14.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: alembic_version; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.alembic_version (
    version_num character varying(32) NOT NULL
);

...

Now run this command to create the database on heroku

heroku pg:psql --app heroku_app_name < database_output_name

If you want to reset your database you can run this command

heroku pg:reset -a heroku_app_name

Now you can check the database on heroku by clicking on postgres link Please check this answer for more detail: pg_restore: error: unrecognized data block type (0) while searching archive while trying to import postgres database to heroku

Sidle

Answer 6

for anyone who might be facing the problem of not being able to push their local postgreSQL to heroku (WINDOWS), hear me out

1. Create an Addon on Heroku using GUI or CLI
2. OPEN CMD AS ADMINISTRATOR
3. First, dump SCHEMA ONLY using pg_dump -s -f <DB_OUTPUT_NAME_ONLY_SCHEMA> --no-owner --no-acl -U <USERNAME> <LOCAL_DB_NAME>
4. SECOND, restore your db on heroku using heroku pg:psql --app <APP_NAME> < <DB_OUTPUT_NAME_ONLY_SCHEMA>
5. THIRD, dump DATA ONLY using pg_dump -a -f <DB_OUTPUT_NAME_ONLY_DATA> --no-owner --no-acl -U <USERNAME> <LOCAL_DB_NAME>
6. FORTH, restore your data on heroku usingheroku pg:psql --app <APP_NAME> < <DB_OUTPUT_NAME_ONLY_DATA>

Streamlet