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

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>

Answer 2

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

Answer 3

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 4

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 5

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 6

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