Complete Migration?

[deanfourie1]

I would like to migrate all data from a previous Netbox instance to a new one?

What's the best way to do this?

Thanks

[mtinberg]

Dump the database from postgresql on the old instance (referencing the postgresql documentation for specifics), copying any shared/uploaded files and restoring those on the new server, running upgrade.sh if this also changes the version of netbox installed. once you have a good backup, you can run through the restore process as many times as you need to work out any kinks and make sure you understand the process, without losing data. I would also be advised when testing a new instance that if you are using webhooks to signal external systems, or scripts/cronjobs that make changes that you are only allowing one of those to run at a time, so you aren't creating duplicate/conflicting changes in prod from your in-setup system. — Mark Tinberg ***@***.***> Division of Information Technology-Network Services University of Wisconsin-Madison

…

________________________________ From: deanfourie1 ***@***.***> Sent: Monday, October 23, 2023 5:11 AM To: netbox-community/netbox ***@***.***> Cc: Subscribed ***@***.***> Subject: [netbox-community/netbox] Complete Migration? (Discussion #14104) I would like to migrate all data from a previous Netbox instance to a new one? What's the best way to do this? Thanks — Reply to this email directly, view it on GitHub<#14104>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAS7UM243WBE46DEATK3MKTYAY7ELAVCNFSM6AAAAAA6L2AUHKVHI2DSMVQWIX3LMV43ERDJONRXK43TNFXW4OZVG43DMOBSGU>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[deanfourie1]

Thanks for the reply. There is one curveball.

I am running netbox from the docker-compose method for the old installation. I have now migrated to a non-docker standard debain installation.

How can I access the database within the docker container?

I found this guide and am trying this, but cannot access the database inside the docker container.

Thanks

`I finally got it to work so I'm posting the (hopefully) newbie-proof instructions here:

Dump the DB with pg_dump --username netbox --password --host localhost netbox > netbox.sql

Copy to new server

Set the secret_key in the [configuration.py](https://configuration.py/) to the same value you used on the original server

Stop the NetBox Service with sudo systemctl stop netbox netbox-rq

Drop DB on new server, create a new blank, grant rights and import the dump:
sudo -u postgres psql -c 'drop database netbox'
sudo -u postgres psql -c 'create database netbox'
sudo -u postgres psql -c 'grant all privileges on database netbox to netbox
sudo -u postgres psql netbox < netbox.sql

Go to the python virtual environment with source /opt/netbox/venv/bin/activate and run the migration script with python3 [manage.py](https://manage.py/) migrate

Restart Netbox with sudo systemctl start netbox netbox-rq and sudo systemctl enable netbox netbox-rq`

[deanfourie1]

Also, I don't remember setting a database password in the installation method

[deanfourie1]

FYI, more details of the migration.

I want to go from version 3.6.1 to version 3.6.4. Is this as simple as a DB export and import? No version stepping required in between?

The 3.6.1 installation is with docker-compose. I cannot seem to export the db using

pg_dump --username netbox --password --host localhost netbox > netbox.sql

Using password netbox, I receive the following error.

pg_dump: error: connection to database "netbox" failed: could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

[bogdancordos]

You will need to access the DB through docker exec:
docker exec -t pg_dumpall -c -s -U > <host/location/and/filename>

In your case, this might look like this:
docker exec -t netbox pg_dumpall -c -s -U netbox > netbox.sql

[deanfourie1]

Thanks, initially that didn't work, looks like my container was named something other then netbox.

Ran docker with -d, and ran docker compose ps, the container has to be running for this to list the containers.

My database was named netbox-docker-postgres-1, so used

docker exec -t netbox-docker-postgres-1 pg_dumpall -c -s -U netbox > netbox.sql

cat netbox.sql looks to have done the trick.

Thanks for your help!

[deanfourie1]

Also, on a side note, using the Export option as CSV and importing throws this error.

Thougth it might have been a version mismatch error so updated the docker to the latest version, versions are now matching, yet same error.

Is this a bug?

[bogdancordos]

Make sure that your headers doesn't contain capitals, as NetBox won't recognize "Name" as "name". The CSV headers must match the field options format. Also, the CSV must not contain columns that are not under "Field Options" list.

[deanfourie1]

But this CSV was exported from within netbox

[bogdancordos]

I know, but the exports in NetBox are mainly created for external use. For importing, you need to reformat the file.

[deanfourie1]

We really need a data export option built in, to side step this whole DB migration process. I'm sure you clever cats can come up with a simple front end GUI export/import option pretty easily (i'm no dev).

Loving netbox through! Great work!

[deanfourie1]

Even a simple backend shell script that can be executed easily to automate the migration process. Just my 2 cents.

[deanfourie1]

I'm having some issues with the import.

Heres the error.

root@netbox-local:/opt/netbox# sudo -u postgres psql netbox < netbox.sql
SET
SET
SET
ERROR:  cannot drop the currently open database
ERROR:  role "netbox" cannot be dropped because some objects depend on it
DETAIL:  privileges for database netbox
ERROR:  role "netbox" already exists
ALTER ROLE
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
UPDATE 1
DROP DATABASE
ERROR:  option "locale_provider" not recognized
LINE 1: ...late1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PRO...
                                                             ^
ERROR:  database "template1" does not exist
\connect: FATAL:  database "template1" does not exist
root@netbox-local:/opt/netbox#

[deanfourie1]

These are the steps taken

sudo systemctl stop netbox netbox-rq
sudo -u postgres psql -c 'drop database netbox'
sudo -u postgres psql -c 'create database netbox'
sudo -u postgres psql -c 'grant all privileges on database netbox to netbox'
sudo -u postgres psql netbox < netbox.sql

[deanfourie1]

And also,

psql: error: FATAL: role "root" does not exist

[deanfourie1]

More testing, trying, failing.

postgres=# CREATE DATABASE netbox ENCODING 'UTF8' LC_COLLATE='C.UTF-8' LC_CTYPE='C.UTF-8';
ERROR:  template database "template1" does not exist

[deanfourie1]

Also tried,

UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';
DROP DATABASE template1;
CREATE DATABASE template1 TEMPLATE = template0;
UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';

[deanfourie1]

Anyone, would hate to have to do everything again from scratch.

Thanks

[deanfourie1]

Everytime I attempted to import the database with

sudo -u postgres psql netbox < netbox.sql

I can see that template1 gets deleted with a \l in psql, there is no template1, before running the import, template 1 is there.

This is now beginning to get very frustrating.

[deanfourie1]

Guess im on my own with this one.

[jcollie]

This is really a PostgreSQL issue, so you might get better help in a PostgreSQL forum. That said, if template1 is getting deleted it's because the import has commands in it to delete it. The most common PostgreSQL dump format is just a text file with a bunch of SQL commands in it to recreate the database. You should be able to open it with a text editor if it's not too large and remove any commands that mess with template1.

[deanfourie1]

Ok thanks, I hadn't even thought of doing this. Inside the SQL file I found the following.

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.utf8';

This is the point at which it fails, so I can now narrow down the issue to be directly related to the above database format that seems to be incorrect. Further more, seems to be failing with

LOCALE_PROVIDER = libc
ERROR: option "locale_provider" not recognized

Any ideas?

[mtinberg]

I would remove that from the dump, the dump should just be of the netbox database, not postgresql defaults which may be different between releases or distro builds (I don't know enough about postgresql administration to have a sense of how likely that is), the netbox application database shouldn't have anything version/install specific in it as it supports a wide range of versions from pg12 to current I think. And yeah, the dump from pg (and mysql) is just the text of the SQL commands so you can edit it with sed or whatever is your favorite editor to make changes before import. Using the SQL text which is a stable language is highly portable. — Mark Tinberg ***@***.***> Division of Information Technology-Network Services University of Wisconsin-Madison

…

________________________________ From: deanfourie1 ***@***.***> Sent: Friday, October 27, 2023 5:16 PM To: netbox-community/netbox ***@***.***> Cc: Mark Tinberg ***@***.***>; Comment ***@***.***> Subject: Re: [netbox-community/netbox] Complete Migration? (Discussion #14104) Ok thanks, I hadn't even thought of doing this. Inside the SQL file I found the following. CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.utf8'; This is the point at which it fails, so I can now narrow down the issue to be directly related to the above database format that seems to be incorrect. Further more, seems to be failing with LOCALE_PROVIDER = libc Any ideas? — Reply to this email directly, view it on GitHub<#14104 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAS7UM2YE3G3LV62ZQDJXNDYBQXEBAVCNFSM6AAAAAA6L2AUHKVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TIMBXHEZTE>. You are receiving this because you commented.Message ID: ***@***.***>

[deanfourie1]

I gave up on this.

Please implement a more satisfactory import/export option.

I think it's beneficial.

[jcollie]

Backing up & restoring the PostgreSQL database is really the best option as the devs would need to basically re-invent the wheel to implement some other option. That other option would likely be very buggy to begin with and cause other frustrations.

[deanfourie1]

Fair enough, I'm just saying it could be a useful integration, as we all know the database can become quite big! So a simple export to a file and import would be a great feature.

I will just be sure to backup my container from now on.

[jcollie]

So a simple export to a file and import would be a great feature.

But that's exactly what a PostgreSQL backup is. I'd suggest setting up a small development instance completely separate from production data and do some experimenting there to familiarize yourself with how PostgreSQL backups and restores work.

[mtinberg]

I'm sorry to hear that. I know this doesn't solve your immediate problem, but maybe a hosted service where someone else can make it their full time job to worry about the server software/db install/upgrade/backup/maintenance makes more sense for your site. — Mark Tinberg ***@***.***> Division of Information Technology-Network Services University of Wisconsin-Madison

…

________________________________ From: deanfourie1 ***@***.***> Sent: Monday, October 30, 2023 3:21 PM To: netbox-community/netbox ***@***.***> Cc: Mark Tinberg ***@***.***>; Comment ***@***.***> Subject: Re: [netbox-community/netbox] Complete Migration? (Discussion #14104) I gave up on this. Please implement a more satisfactory import/export option. I think it's beneficial. — Reply to this email directly, view it on GitHub<#14104 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAS7UM6WTZV4WTXACUKNNZLYCAD5FAVCNFSM6AAAAAA6L2AUHKVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TIMRYGE4TA>. You are receiving this because you commented.Message ID: ***@***.***>

[deanfourie1]

Its weird I got a successful import but no data showed up.

Also, looking through the initial SQL files, I could see nothing specific to my site, no IP addresses etc in the SQL file.

Yet its the only file I could produce of any significant size.

[slindenberg]

ERROR: option "locale_provider" not recognized. I got it fixed by upgrading to Postgres 15.

[mtinberg]

I was setting up a new instance with PostgreSQL 16 on RHEL9 instead of PostgreSQL 12 on RHEL7 and ran into this error, when postgres is initially installed and the default db files are created with `initdb` it imports language definitions from the host OS and newer versions of postgresql support using libc or libicu for these definitions and the format for describing them has changed somewhat. I changed my postgresql_collation from en_US.UTF-8 to C.utf8 as that was what was listed in the DB and was able to install Netbox. https://www.postgresql.org/docs/current/locale.html It was a bit of a rabbit hole — Mark Tinberg ***@***.***> Division of Information Technology-Network Services University of Wisconsin-Madison

…

________________________________________ From: Stefan Lindenberg ***@***.***> Sent: Tuesday, April 2, 2024 5:52 AM To: netbox-community/netbox Cc: Mark Tinberg; Comment Subject: Re: [netbox-community/netbox] Complete Migration? (Discussion #14104) ERROR: option "locale_provider" not recognized. I got it fixed by upgrading to Postgres 15. — Reply to this email directly, view it on GitHub<https://urldefense.com/v3/__https://github.com/netbox-community/netbox/discussions/14104*discussioncomment-8982443__;Iw!!Mak6IKo!IIyCY4gzcz_oi1zCvHnU8nWODen7t3CEmBo-tKAXqGrTFPZoL1FecFH4m-MGTqSny2cvrlKZ5HdD5K6K5aMICMNT$>, or unsubscribe<https://urldefense.com/v3/__https://github.com/notifications/unsubscribe-auth/AAS7UM4SNIVMNZU4RESZICDY3KEVBAVCNFSM6AAAAAA6L2AUHKVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DSOBSGQ2DG__;!!Mak6IKo!IIyCY4gzcz_oi1zCvHnU8nWODen7t3CEmBo-tKAXqGrTFPZoL1FecFH4m-MGTqSny2cvrlKZ5HdD5K6K5bXHYVyu$>. You are receiving this because you commented.Message ID: ***@***.***>