Migrate to netbox-docker using different Postgresql version

[Stephan Boldt]

Hello,

I want to migrate the netbox which is currently installed on a Centos 7 VM together with Postgres 9.6 to a containerized netbox using the netbox-docker images. which use Postgres 12. Both are running the same Netbox level (2.10.4) but I don't know how to  migrate the data to the conainerized version.

Export/import using pg_dump does not work. It throws a lot of errors ("...already defined..." etc) which I don't get if I restore the dump on the source system. After importing into Postgres12 Netbox doesn't start anymore. The main issue seems to be that the old system has Postgres 9 and the new one Postgres 12. But I assume there has to be a solution for migrating to a new server with a higher Postgres level, that I just cannot see. :(

Can you tell me what I have to do to migrate the data from the old to the new installation?

kind regards

Stephan

[Brian Candler]

If I google "migrate data postgres 9 to postgres 12" then the top two hits are:

https://www.postgresql.org/docs/9.0/migration.html

https://www.postgresql.org/docs/12/upgrading.html

"To dump data from one major version of PostgreSQL and reload it in another, you must use pg_dump"

So you're definitely approaching it the right way.  If you're getting errors, then I suggest you show the exact specific errors, and the lines of SQL which are causing them.  Note that you can use a pg_dump binary from a newer version of postgres, on an older version.

Incidentally, I don't know which netbox-docker image you're looking at, but the one I can see doesn't include postgres:

https://github.com/netbox-community/netbox-docker

Rather, postgres is a *separate* container image, which you bring up as its own container, e.g. using docker-compose.

https://github.com/netbox-community/netbox-docker/blob/release/docker-compose.yml#L44

You could easily change that to postgres 9.6 if you prefer.

Regards,

Brian.

[Stephan Boldt]

Brian, thanks for your answer, 

I found the following page very helpful: 

https://github.com/netbox-community/netbox-docker/issues/262

here are the exact commands to successfully dump and restore the database, especially in a netbox-docker context: 

Backup

docker-compose exec -T netbox tar c -zvf - -C /opt/netbox/netbox/media ./ > "${BACKUP_PATH}/${BACKUP_DATE}_media.tgz" 

docker-compose exec -T postgres sh -c 'pg_dump -v -Fc -U $POSTGRES_USER $POSTGRES_DB' > "${BACKUP_PATH}/${BACKUP_DATE}.pgdump" 

Restore

docker-compose up -d postgres 

docker-compose exec -T postgres sh -c 'pg_restore -v -Fc -c -U $POSTGRES_USER -d $POSTGRES_DB' < "${BACKUP_PATH}/${BACKUP_DATE}.pgdump" 

docker-compose up -d 

docker-compose exec -T netbox tar x -zvf - -C /opt/netbox/netbox/media < "${BACKUP_PATH}/${BACKUP_DATE}_media.tgz" 

The  thing I did a bit differently was that I first startet up _all_ containers with "docker-compose up -d" waited for all of them to initialize and then shut them down with "docker-compose stop". After starting only the postgres container as described I logged into the container with "docker exec -it  netbox-docker_postgres_1 /bin/bash", connected to the DBMS, deleted and recreated the netbox DB:  

psql -U netbox postgres

drop database netbox;

create database netbox; 

Afterwards restore worked quite flawless, but I had to run the restore two or three times because during the first run many errors occurred due to references to not yet existing entries. In the end the only error that was still present was one complaining about the missing "postgres role" which simply is not there when using netbox-docker. But this shouldn't be any problem.

Furthermore I have to add that with the commands above the restore was successfull both with Postgres 9.6 and 12.5 (pg_dump version 9.6 -> pg_restore version 9.6/12.5)

The main issue I was running into yesterday was that for reasons unknown someone had set up the old netbox installation I dumped the DB from was configured to not use the db "netbox" but "netbox2" - probably because "netbox" was broken at some point in the past. The commands above use environment variable $POSTGRES_DB which reflects the proper db name on the system the command is run on ("netbox2" on the source, "netbox" on the target).

I hope these hints might be helpful for somebody running into similar migration issues as I did. :-)

regards

Stephan

[Stephan Boldt]

Sorry, one point was a bit different than I just wrote: Because the export was done on a non-containerized installation the command I used to export the db had to be a bit different than stated in my mail and in the linked document (-> no docker-compose invocation and stating the precise DB name that was "netbox2" in my case):

pg_dump -v -Fc -U netbox netbox2 > netbox_dump.sql

regards,

Stephan