netbox database restore

[seihoo wong]

Hi All,

i'll need your help as i have a backup call "pro3DB .sql" in hand (netbox 2.10.8 version) and i'd like to restore/replicate it to my new development system which si running 2.11.0. this is the command i ran.
 "sudo -u postgres psql -U netbox netboxdb < pro3DB.sql"

it ran with alot of erro but in different style.

1.)

ERROR:  relation "auth_group" already exists

ALTER TABLE

ERROR:  relation "auth_group_id_seq" already exists

ALTER TABLE

ALTER SEQUENCE

ERROR:  relation "auth_group_permissions" already exists

2.) 

Query buffer reset (cleared).

invalid command \nFor

Query buffer reset (cleared).

invalid command \nsup...@mail.com

Query buffer reset (cleared).

invalid command \supe...@mail.com

invalid command \.

COPY 0

COPY 112

COPY 0

ERROR:  null value in column "custom_field_data" violates not-null constraint

DETAIL:  Failing row contains (313, Console, 586, null, , , Console, , null, null, null, null, null, null, null, null).

CONTEXT:  COPY dcim_consoleport, line 1: "313   Console 586     \N                      Console         \N      \N      \N"

ERROR:  insert or update on table "dcim_consoleporttemplate" violates foreign key constraint "dcim_consoleporttemplate_device_type_id_075d4015_fk"

DETAIL:  Key (device_type_id)=(8) is not present in table "dcim_devicetype".

ERROR:  null value in column "custom_field_data" violates not-null constraint

DETAIL:  Failing row contains (12, TTY 1/11, 13, 5, , , TTY 00000001/00000011, , 498, 33, null, null, null, null, null, null).

CONTEXT:  COPY dcim_consoleserverport, line 1: "12      TTY 1/11        13      5    

3.) 

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

Query buffer reset (cleared).

invalid command \nWhen

invalid command \N

invalid command \N

invalid command \N

invalid command \N

4.) 

DETAIL:  Key (content_type_id)=(89) is not present in table "django_content_type".

ERROR:  duplicate key value violates unique constraint "django_content_type_pkey"

DETAIL:  Key (id)=(1) already exists.

CONTEXT:  COPY django_content_type, line 1

ERROR:  duplicate key value violates unique constraint "django_migrations_pkey"

DETAIL:  Key (id)=(1) already exists.

CONTEXT:  COPY django_migrations, line 1

5.) 

ALTER TABLE

ERROR:  column "group_id" referenced in foreign key constraint does not exist

ALTER TABLE

ALTER TABLE

ALTER TABLE

ERROR:  there is no unique constraint matching given keys for referenced table "dcim_rackgroup"

ALTER TABLE

ALTER TABLE

ALTER TABLE

ERROR:  constraint "dcim_rackreservation_user_id_0785a527_fk_auth_user_id" for relation "dcim_rackreservation" already exists

ERROR:  constraint "dcim_rearport__cable_peer_type_id_cecf241c_fk_django_co" for relation "dcim_rearport" already exists

ALTER TABLE

ALTER TABLE

ALTER TABLE

ALTER TABLE

ALTER TABLE

ALTER TABLE

ERROR:  constraint "dcim_sitegroup_parent_id_533a5e44_fk_dcim_sitegroup_id" for relation "dcim_sitegroup" already exists

ALTER TABLE

ERROR:  constraint "django_admin_log_content_type_id_c4bce8eb_fk_django_co" for relation "django_admin_log" already e

when it complete, i can see ONLY user account and "cable" table was populated the rest are not.

echo '\ds' | sudo -u postgres psql netboxdb| grep extras

 public | extras_configcontext_cluster_groups_id_seq     | sequence | netbox

 public | extras_configcontext_clusters_id_seq           | sequence | netbox

 public | extras_configcontext_device_types_id_seq       | sequence | userA

 public | extras_configcontext_id_seq                    | sequence | netbox

 public | extras_configcontext_platforms_id_seq          | sequence | netbox

 public | extras_configcontext_regions_id_seq            | sequence | netbox

 public | extras_configcontext_roles_id_seq              | sequence | netbox

 public | extras_configcontext_site_groups_id_seq        | sequence | userA

 public | extras_configcontext_sites_id_seq              | sequence | netbox

 public | extras_configcontext_tags_id_seq               | sequence | netbox

 public | extras_configcontext_tenant_groups_id_seq      | sequence | netbox

 public | extras_configcontext_tenants_id_seq            | sequence | netbox

 public | extras_customfield_content_types_id_seq        | sequence | userA

 public | extras_customfield_id_seq                      | sequence | netbox

 public | extras_customfield_obj_type_id_seq             | sequence | netbox

 public | extras_customlink_id_seq                       | sequence | netbox

 public | extras_exporttemplate_id_seq                   | sequence | netbox

 public | extras_imageattachment_id_seq                  | sequence | netbox

 public | extras_jobresult_id_seq                        | sequence | netbox

 public | extras_journalentry_id_seq                     | sequence | userA

 public | extras_objectchange_id_seq                     | sequence | netbox

 public | extras_tag_id_seq                              | sequence | netbox

 public | extras_taggeditem_id_seq                       | sequence | netbox

 public | extras_webhook_content_types_id_seq            | sequence | userA

 public | extras_webhook_id_seq                          | sequence | netbox

 public | extras_webhook_obj_type_id_seq                 | sequence | netbox

 

Any one encounter this issue before?

[Brian Candler]

Drop the database before restoring.  See: https://netbox.readthedocs.io/en/stable/administration/replicating-netbox/#load-an-exported-database

[seihoo wong]

i did drop and recreate the db,
then the Dump as restrored,

then i went ahead to execute the --> python3 manage.py runserver 0.0.0.0:8000 --insecure to test it out....

but then it throw me an msg of asking me to execute the migrate as below:

/opt/netbox/netbox$ python3 manage.py runserver 0.0.0.0:8000 --insecure

Performing system checks...

System check identified no issues (0 silenced).

You have 28 unapplied migration(s). Your project may not work properly until you apply the migrations for app(s): circuits, dcim, extras, ipam, secrets, tenancy, users, virtualization.

Run 'python manage.py migrate' to apply them.

September 28, 2021 - 10:24:30

Django version 3.2, using settings 'netbox.settings'

Starting development server at http://0.0.0.0:8000/

Quit the server with CONTROL-C.

which i did .. and then it throw me the error of :

/opt/netbox/netbox$ python manage.py migrate

Operations to perform:

  Apply all migrations: admin, auth, circuits, contenttypes, dcim, extras, ipam, secrets, sessions, taggit, tenancy, users, virtualization

Running migrations:

  Applying extras.0054_standardize_models... OK

  Applying extras.0055_objectchange_data... OK

  Applying dcim.0123_standardize_models... OK

  Applying dcim.0124_mark_connected... OK

  Applying dcim.0125_console_port_speed... OK

  Applying dcim.0126_rename_rackgroup_location...Traceback (most recent call last):

  File "/opt/netbox-2.11.0/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

psycopg2.errors.DuplicateTable: relation "dcim_location" already exists

The above exception was the direct cause of the following exception:

which sounds like a recursive problem one and follow by another and these are the step.

1.) drop netboxdb
2.) create netboxdb
3.) execute restore : sudo -u postgres psql -U netbox netboxdb < pro3DB.sql (which looking good)
4.) run : python3 manage.py runserver 0.0.0.0:8000 --insecure
5.) prompt to run : python manage.py migrate
6.) got an error of " psycopg2.errors.DuplicateTable: relation "dcim_location" already exists"

7.) then again  drop the db and create and repeat the same process.... and same outcome.. i'm puzzle.

[Brian Candler]

First try it in this order:

- Do stpes 1-3

- Do step 5 (or better, run "./upgrade.sh" instead)

- Only then start Netbox

That shouldn't fail.  If it does, your database has got into a bad state: dcim_location shouldn't exist in Netbox prior to 2.11.  Did you ever upgrade to any beta or development version?

[seihoo wong]

i believe it could be the "after effect" when i initially tried to install V3.0 now issue got resolved by manually drop the problematic table. now i'm able to upgrade from 2.10.8 --> 2.11 --> 3.0 just fine. thank you !