Migrating DB to a new server

[ryan guillory]

- Built out NetBox on a VM; up and running with no info in the db

- Upgraded old serve; UI broke 

- Trying to work around by salvaging what I can from the old DB and dumping it over on the new machine. 

- Here is what I have done so far: 

-- mkdir -p /var/backup/netbox

-- chmod 700 /var/backup/netbox

-- su -c 'pg_dump' postgres | gzip -9 > /var/backup/netbox/netbox.$(date +%Y%m%d%H%M).gz

Then I was having some issues with scp, so I ended up using pscp to copy the compressed file old server > local machine and then local machine > new server

and then ran:

gzip -dc /var/backup/netbox/netbox.201806110828 | | su -c 'psql netbox' postgres 

Output of the above command is below: 

-- PostgreSQL database dump

--

-- Dumped from database version 9.5.8

-- Dumped by pg_dump version 9.5.8

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET row_security = off;

--

-- Name: postgres; Type: COMMENT; Schema: -; Owner: postgres

--

COMMENT ON DATABASE postgres IS 'default administrative connection database';

--

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:

--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:

--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--

-- Name: public; Type: ACL; Schema: -; Owner: postgres

--

REVOKE ALL ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON SCHEMA public FROM postgres;

GRANT ALL ON SCHEMA public TO postgres;

GRANT ALL ON SCHEMA public TO PUBLIC;

--

-- PostgreSQL database dump complete

--

netbox@netbox:/$

Still nothing, is there something I am doing wrong? any recommendations?

-TIA

[Brian Candler]

gzip -dc /var/backup/netbox/netbox.201806110828 | | su -c 'psql netbox' postgres 

Problem 1: double vertical bar is the "or" operator in Unix shell.  It runs the left-hand command, and then only runs the right-hand one if the left-hand one failed (return code > 0)

To pipe the stdout of one command into the stdin of another, then you need to use a single vertical bar.

 

Output of the above command is below: 

...

--

-- Name: postgres; Type: COMMENT; Schema: -; Owner: postgres

--

COMMENT ON DATABASE postgres IS 'default administrative connection database';

Problem 2: you dumped the wrong database.  You dumped the database called "postgres" rather than the database called "netbox".  The "postgres" database, as it says, is an internal administration-only database.

Hopefully you haven't already destroyed your original database, because you'll need to re-run the command which exports it - this time export the right one.  And also have a look inside the dump file to check that it contains the expected information about your exported devices/interfaces etc.

[ryan guillory]

So, I went back and ran a pg_dump on the netbox database and double checked the contents of the file and it looks like it has everything I need. The only problem I am having now is the inability to successfully import that information to the new server. 

Here's what I did: 

pg_dump -U netbox -W -h localhost | gzip -9 > /var/backup/netbox/netbox.$(date +%Y%m%d%H%M).gz # for some reason this was the only way I was able to get it to work 

The contents of this file were correct and reflected the information that I needed, and then I ran this on the new VM after copying the file over:

gzip -dc /var/backup/netbox/netbox.201806210938.gz | psql -U netbox -h localhost -Wd netbox

I was getting some errors, basically these ones

ERROR:  duplicate key value violates unique constraint "circuits_provider_pkey"

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

CONTEXT:  COPY circuits_provider, line 1

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

DETAIL:  Failing row contains (1, Console Server, console-server, 009688, null).

CONTEXT:  COPY dcim_devicerole, line 1: "1      Console Server  console-server  009688"

ERROR:  insert or update on table "dcim_devicetype" violates foreign key constraint "dcim_devicetype_manufacturer_id_a3e8029e_fk_dcim_manu"

DETAIL:  Key (manufacturer_id)=(9) is not present in table "dcim_manufacturer".

ERROR:  multiple primary keys for table "dcim_platform" are not allowed

ERROR:  relation "dcim_platform_slug_key" already exists

Is there I can get the new DB to accept the old DB information?

[ryan guillory]

Also, old server is running psql 9.5.8 and the new VM server is running 9.5.12 (I am sure this is a problem, but I am trying to find a way to work around this and any help is appreciated). 

[Brian Candler]

At a guess, maybe a previous partial import failed, and you need to delete and recreate the 'netbox' database on the target host?  Don't use netbox migrations to recreate it first, start with completely empty netbox database (just do 'create database netbox' and 'grant ...')

Anyway, this isn't really a netbox question any more; it's a postgres administration question.

[Gatis Visnevskis]

Check again, if you have correct encoding (UTF-8)

$ psql -l

I had similar import issues, had almost run out of ideas, and then figured that Ubuntu was installed with default LATIN locale.

All that process took for me several months to find root cause, and to change that in postgres was pretty hard.

Documentation is sometimes wrong, or does not exist.

Maybe helps:

Change postgres default template0 to UTF8 encoding

https://gist.github.com/ffmike/877447

Gatis

[ryan guillory]

Thanks for you help Brian. I'm not a database guy (obviously because of the hard time I am having with this issue), if this is the wrong place to ask for help then, if you don't know that's all right. I'm just looking for a sense of direction. 

[ryan guillory]

Hey Gatis, thanks for the input. I double checked the encoding and it is correct; UTF8

On Friday, June 22, 2018 at 2:14:18 AM UTC-7, Gatis Visnevskis wrote:

Check again, if you have correct encoding (UTF-8)

$ psql -l

[Brian Candler]

On Thursday, 21 June 2018 22:58:02 UTC+1, ryan guillory wrote:

old server is running psql 9.5.8 and the new VM server is running 9.5.12 (I am sure this is a problem

This is very unlikely to be a problem; psql backwards compatibility is very good.  But if there is another difference between the two systems (e.g. you're exporting from CentOS and importing to Ubuntu) it's possible the two systems built postgres with different options, like encoding as already mentioned.

Importing databases works fine for me, so you identify what it is that's different or unusual about what you're doing.  I can't guess this.

What you need to do is describe in more detail the source and target systems (not just the psql versions but also OS version), and show a transcript of *exactly* what commands you're doing to:

- delete the old target database

- create the new target database

- import the exported database

To me it looks very much like you're not starting from an empty target netbox database.

[ryan guillory]

Old system (SOURCE): 

DISTRIB_ID=Ubuntu

DISTRIB_RELEASE=16.04

DISTRIB_CODENAME=xenial

DISTRIB_DESCRIPTION="Ubuntu 16.04.3 LTS"

NAME="Ubuntu"

VERSION="16.04.3 LTS (Xenial Xerus)"

ID=ubuntu

ID_LIKE=debian

PRETTY_NAME="Ubuntu 16.04.3 LTS"

VERSION_ID="16.04"

HOME_URL="http://www.ubuntu.com/"

SUPPORT_URL="http://help.ubuntu.com/"

BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"

VERSION_CODENAME=xenial

UBUNTU_CODENAME=xenial

New System (TARGET): 

DISTRIB_ID=Ubuntu

DISTRIB_RELEASE=16.04

DISTRIB_CODENAME=xenial

DISTRIB_DESCRIPTION="Ubuntu 16.04.4 LTS"

NAME="Ubuntu"

VERSION="16.04.4 LTS (Xenial Xerus)"

ID=ubuntu

ID_LIKE=debian

PRETTY_NAME="Ubuntu 16.04.4 LTS"

VERSION_ID="16.04"

HOME_URL="http://www.ubuntu.com/"

SUPPORT_URL="http://help.ubuntu.com/"

BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"

VERSION_CODENAME=xenial

UBUNTU_CODENAME=xenial

Apologies for the lack of information, but I am new to seeking help through forums and such: 

So here is what I have come up with: 

DROP DATABASE [IF EXISTS] netbox

sudo -u postgres psql

CREATE DATABASE netbox;

CREATE USER netbox WITH PASSWORD '$PASSWORD';

GRANT ALL PRIVILEGES ON DATABASE netbox TO netbox;

once I do this ( assuming these are the correct steps ) do I have to rerun the db migrations and load initial data? or would I want to import my backup file?

Thanks for you patience and help. 

[Brian Candler]

On Monday, 25 June 2018 18:11:08 UTC+1, ryan guillory wrote:

once I do this ( assuming these are the correct steps ) do I have to rerun the db migrations and load initial data? or would I want to import my backup file?

You'd want to import your backup file *without* doing db migrations.  If you scan through it you'll see it just contains a load of create table and insert statements.

Once the import is complete, if the target system has a newer version of netbox than the one you exported from, then you'll want to run migrations (./upgrade.sh should do it).  You can't restore to an older version of netbox.  You definitely don't want to load initial data because that will clash with what's in your export.

[ryan guillory]

Got it, thank you for the clarification Brian. I am a little skeptical about using the ./upgrade.sh script as that is how I got myself into this mess in the first place, I would rather run the steps individually in hopes of avoiding chances of breaking the working VM I have. 

Again, thanks for the help I really do appreciate it it as I have been working on fixing this for the better part of the last month. 

[ryan guillory]

Just a quick update: 

Dropping the netbox DB and then recreating an empty one and dumping the compressed file into the new VM worked ( after running python3 manage.py migrate that is ). Everything works as at should now. Thanks for all of the help Brian. 

[Brian Candler]

Thanks for reporting success!