How to restore the PostgreSQL database of heroku to PostgreSQL in docker

[tortoo]

I've been running huginn on heroku and the database is PostgreSQL. Now I've got a VPS with huginn and PostgreSQL installed on docker. I've already backed up and exported the database from heroku website. I want to know could this data be used on docker and how to restore it.

[dsander]

I am not sure how Heroku exports databases, if the backup only contains the tables and data (without creating the database itself) you first need to drop the current database in your docker container, recreate it with an empty one and then restore your backup:

# This assumes huginn_postgres_1 is the name of the postgres container
docker exec -i huginn_postgres_1 psql -U postgres -c "DROP DATABASE huginn"
docker exec -i huginn_postgres_1 psql -U postgres -c "CREATE DATABASE huginn"
cat your_backup.sql | docker exec -i huginn_postgres_1 psql -U postgres huginn

[tortoo]

@dsander I searched a lot and still couldn't find the data file on the docker container😖. I deployed huginn on CentOS 7 with this app and following this manual. I only installed the PostgreSQL rather than MySQL. The command docker ps shows huginn and PostgreSQL are all running well and the huginn website with 7 Agents are opened correctly. I think the method to deploy huginn is very simple and efficient. And the backup database is downloaded from Heroku data web page.

The file name is xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx with no extension name. The file is the same as another one created by using heroku pg:backups:url.

I also find this reminder This will get you up and going as fast as possible, but in production you shouldn't run your application under the postgres user on this page . Can you tell me why shouldn't use PostgreSQL? And you said Storing the whole Event in the memory would create one massive row in the database which could work for PostgreSQL (not sure if Heroku has a limit there) but for mysql would probably reach the column size limit pretty quicklyhere. Maybe I can only use PostgreSQL because I need a massive row joined with lots of events to do some counting with Liquid. Till now I don't know how to choose MySQL and PostgreSQL.

I also don't know how to choose CentOS and Ubuntu. I need it running more stably and less memory usage and more easy to solve the problem. Can you give me some advice? 😹

[dsander]

@dsander I searched a lot and still couldn't find the data file on the docker container😖

Which data file, didn't you download the database backup from heroku?

I looked at the Heroku documentation and those commands should work (run them on the docker host machine):

# first stop the huginn container
docker stop <nameofthehuginncontainer>
# This assumes huginn_postgres_1 is the name of the postgres container
docker exec -i huginn_postgres_1 psql -U postgres -c "DROP DATABASE huginn"
docker exec -i huginn_postgres_1 psql -U postgres -c "CREATE DATABASE huginn"
cat your_backup.dump | docker exec -i huginn_postgres_1 pg_restore --verbose --clean --no-acl --no-owner -U postgres -d huginn

Can you tell me why shouldn't use PostgreSQL?

That wiki page looks a bit dated, the recommendation is correct though (but only applies when not using docker) applications should not be under the postgres user but use their own.

Till now I don't know how to choose MySQL and PostgreSQL.

In my experience PostgreSQL causes less headache than MySQL.

[tortoo]

@dsander Tried many times and finally solved this😹. Hope this could help others.

First, get the data file from heroku.

$ heroku pg:backups:capture --app my_app_name
$ heroku pg:backups:url b003 --app my_app_name
https://xfrtu.s3.amazonaws.com/abc

Second, download the data file into host.

[root@host ~]# wget https://xfrtu.s3.amazonaws.com/abc  -O backup.dump

Third, drop database.

[root@host ~]# docker stop huginn-xxxxxx
[root@host ~]# docker exec -i postgres-xxxxxx psql -U huginn -c "DROP DATABASE huginn"
ERROR:  cannot drop the currently open database

Then, I jumped over the step and did the next.

[root@host ~]# cat backup.dump | docker exec -i postgres-xxxxxx pg_restore --verbose --clean --no-acl --no-owner -U huginn -d huginn
pg_restore: connecting to database for restore
pg_restore: dropping INDEX index_users_on_username
......
pg_restore: creating INDEX "public.index_users_on_username"

[root@host ~]# docker start huginn-xxxxxx

Done.

[dsander]

Nice!