Complete method of replicate Netbox from a server to a new one

[manuelcalder0n]

Hello, I have Netbox v3.7.4 installed on a server and I want to replicate it in a new server, working as full back up. I have the .sql but when I am going to upload it on the new server I am unable to do it.
Does anyone know how is the complete process of migration? I didn't find it.
Thanks in advance :)

[manuelcalder0n]

user@stnetboxbkp:~$ psql netbox < netbox.sql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "user" does not exist

This is the fatal error it appears

[candlerb]

The complete process is documented here:
https://netboxlabs.com/docs/netbox/en/stable/administration/replicating-netbox/

[candlerb]

Postgresql version?

I think you might need these steps on the target system:

CREATE USER netbox WITH PASSWORD 'XXXXX';
ALTER DATABASE netbox OWNER TO netbox;
-- the next two commands are needed on PostgreSQL 15 and later
\connect netbox;
GRANT CREATE ON SCHEMA public TO netbox;

Maybe not the "create user" one if there was already a netbox user, but since you've dropped and recreated the database I'd expect you need to set the owner and/or grant the schema permissions on the new one you've just created.

It also depends on exactly how you dumped the database. If you use

sudo -u postgres pg_dump --create --clean --if-exists netbox

then you'll find the dump includes the steps

DROP DATABASE IF EXISTS netbox;
CREATE DATABASE netbox WITH TEMPLATE ...etc
ALTER DATABASE netbox OWNER TO netbox;
...
GRANT CREATE ON SCHEMA public TO netbox;

so you should be able to restore it directly with no additional messing around. (That's tested with Postgresql 16.4 under Ubuntu 24.04.1)

[manuelcalder0n]

I have the same Postgres and Ubuntu version, so I would try again in a while and I will come back. Thanks!!!!!

[manuelcalder0n]

Hi,

The error remains... This is the proccess I am following:

On the original device: pg_dump --username netbox --password --host localhost netbox > netbox.sql

Then, using WinSCP I transfer netbox.sql file to target device.

Once on the target device:

postgres=# DROP DATABASE IF EXISTS netbox;
DROP DATABASE

postgres=# CREATE DATABASE netbox;
CREATE DATABASE
postgres=# \q
santander@stnetboxbkp:~$ psql netbox < netbox.sql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "santander" does not exist

pd: (PostgreSQL16.4) (Ubuntu 24.04.2)

[candlerb]

The problem is that the user importing the database ("santander") does not have access to the netbox database.

You could do it as the postgres user:

sudo -u postgres psql netbox <netbox.sql

Or you can do the other steps given before (create user netbox ..., alter database ..., grant create ...) and then authenticate to psql using the "netbox" user credentials, in the same way as you did when dumping the database, e.g.

psql --user netbox --password --host localhost netbox <netbox.sql

[manuelcalder0n]

That is!!! Doing it as postgres user it worked!! Thanks a lot Brian!!