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!

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

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

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

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;

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