Send netdisco-users mailing list submissions to
netdisco-users@lists.sourceforge.net
To subscribe or unsubscribe via the World Wide Web, visit
https://lists.sourceforge.net/lists/listinfo/netdisco-users
or, via email, send a message with subject or body 'help' to
netdisco-users-requ...@lists.sourceforge.net
You can reach the person managing the list at
netdisco-users-ow...@lists.sourceforge.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of netdisco-users digest..."
Today's Topics:
1. Re: [EXTERNAL] Re: How to copy postgresql when Docker'izing
(Eric Bates)
--- Begin Message ---
Yes, thank you.
It's working! w00t!
For the mailing list archive record, here is how I managed it. To some extent,
this will not necessarily be accurate with later versions; but the gist should
be the same. Note also that while I'm quite familiar with MariaDB, I have
little experience with postgresql; so this can certainly be improved upon.
The docker version of postgresql is 13.2 while the Ubuntu version is 16; so
most of the pain occurs because you cannot really downgrade postgres data.
Google is not much help searching for strings like "downgrade postgres x to
postgres y." With good reason.
You cannot rsync the data files (incompatible versions) and you can only use
the ASCII version of a dump because you're going to have to edit the newer
version 16 dump before the older version 13 will import it without errors.
I did all the db manipulation with only the netdisco-postgresql container
running (specify netdisco-postgresql in your "docker compose up" command so
that the other 2 containers (there is a 4th container, but netdisco-do doesn't
start automatically) don't also start and attempt to connect to the postgres.
* dump your existing netdisco database
pg_dump --clean --create -d netdisco -f netdisco_dump_v4_ascii.sql
* copy the dump to your container (I put it in the "pgdata" folder) and
open it in an editor.
* Find the line that begins with "CREATE DATABASE" and delete the text
string "LOCALE_PROVIDER = libc". That is an option which postgresql v13 simply
doesn't grok.
* Thanks to Christian's excellent help, I then deleted the existing
"netdisco" database in the container explicitly. There is a "DROP DATABASE"
command at the beginning of the sql dump due to the "--clean" option; but it's
not clear that it's being executed. The only time I tried I still had the bogus
"LOCALE_PROVIDER = libc" flag in the CREATE command; so it's possible that that
error had something to do with the non-DROP. Here's how to do it manually:
docker exec netdisco-postgresql psql -U postgres postgres
postgres=# drop database netdisco;
And finally hit [ctrl]-d to end the session.
* Now, with the text edit and the pre-deleted database, the import is more
likely to succeed. Reports on Reddit and whatnot suggest that performing the
import within the container is faster than importing from outside the
container; but my netdisco database wasn't big enough to take more than a few
seconds anyway. But here are the two different techniques:
*
external:
docker exec netdisco-postgresql psql -U postgres postgres <
./appdata/pgdata/netdisco-backup.sql
* internal:
docker exec -ti netdisco-postgresql bash
bash# psql -U postgres postgres < /var/lib/postgresql/data/netdisco-backup.sql
* Copy in your deployment.yml to the config folder
Note that cloning your netdisco db does not include postgres authentication. So
you might have to edit deployment.yml with a new password. There is a
"netdisco" user already configured in the db as distributed with the
netdisco-postgresql with a password of 'netdisco'; so you can use that or you
can use the above psql prompt command and change the password to match your
deployment.yml.
Not sure about this, but it appears that "hostname" for the postgresql
parameters should remain commented out in spite of the fact that netdisco needs
to reach the host name 'netdisco-postgresql'. Presumably this is because the
environment variable NETDISCO_DB_HOST set in the compose file will override it?
* Now it's safe to start up the netdisco-web and netdisco-backend
containers.
* check your logs (I like dozzle to see netdisco-web). Hopefully there are
no errors.
Thanks again for the help.
On 4/10/25 15:09, Christian Ramseyer wrote:
This email originated outside of WHOI. Please use caution if clicking on links
or opening attachments.
Yes --clean and --create is even better. I suspect the drop database
fails because the other containers in the docker-compose have
connections open, or because you are connected to it yourself.
I'd try explicitly only starting the one container, initially connect to
the "postgres" db instead of netdisco, drop netdisco manually, then load
the dump from there:
docker-compose up -d
docker-compose stop
docker-compose start netdisco-postgresql
docker-compose exec netdisco-postgresql psql -U postgres postgres
postgres=# drop database netdisco;
DROP DATABASE
CTRL-D to exit
# now you are sure to load into an empty db, you can also just read the
file from the host with redirection like so:
docker-compose exec -T netdisco-postgresql psql -U postgres postgres <
../netdisco-backup.sql
Good luck!
PS the unsupported version (1.15) refers to the archive file format
pg_dump -Fc but I wouldn't bother with that. The plain text dump should
work fine.
On 10.04.2025 20:50, Eric Bates wrote:
Thank you.
Tried using both --clean and --create. It doesn't help. Not even clear
that the two sets of error messages even differ. It's quite confusing
because even tho there is a "DROP netdisco" at the beginning of the dump
file; I still get the "already exists" errors.
Also tried using -Fc and pg_restore instead of redirecting the dump file
to psql. Similar to the rsync attempt pg_restore refuses to run because
of the postgresql version difference:
bash-5.1# pg_restore -d netdisco /var/lib/postgresql/data/netdisco_dump.sql
pg_restore: error: unsupported version (1.15) in file header
That's also kind of confusing because the netdisco-postgresql version is
13.2 and the Ubuntu version is 16. No idea where "1.15" is coming from.
On 4/10/25 13:11, Christian Ramseyer wrote:
This email originated outside of WHOI. Please use caution if clicking on links
or opening attachments.
If you just run pg_dump with -c, it will include drops for all the
objects first and your restore should work a lot better.
Cheers
Christian
On 10.04.2025 15:11, Eric Bates wrote:
Thanks for this amazing program.
We're trying to migrate our Netdisco from a "standard" Ubuntu install to
a Docker container and I'm stuck trying to copy the postgres data.
Ubuntu currently installs postgres 16 and the netdisco-postgresql image
is postgres 13; so, unfortunately, I can't simply rsync the postgresql
data directory. Doing so results in the following postgres error:
netdisco-postgresql | 2025-04-10 12:52:43.716 UTC [1] FATAL: database
files are incompatible with server
netdisco-postgresql | 2025-04-10 12:52:43.716 UTC [1] DETAIL: The data
directory was initialized by PostgreSQL version 16, which is not
compatible with this version 13.4.
So I dumped the data from the existing netdisco:
pg_dump -Unetdisco -W netdisco > netdisco.sql
copied the file into the container's pgdata folder. Fired up just the
postgres image and shelled in:
$ docker compose -f compose-netdisco.yaml up netdisco-postgresql -d
[+] Running 1/1
✔ Container netdisco-postgresql
Started 0.2s
$ docker exec -it netdisco-postgresql bash
bash-5.1#
Finally I attempted to restore the copied data within the container:
bash-5.1# psql -Unetdisco netdisco < /var/lib/postgresql/data/netdisco.sql
The difficulty at this stage is that the restore attempt generates
dozens of errors. Many of them are of the "already exists" variety; so I
don't think I need to worry about them:
ERROR: relation "user_log" already exists
However, there are other errors which look more serious (a small sample):
ERROR: column "leaf" of relation "device_browser" does not exist
invalid command \.
ERROR: multiple primary keys for table "admin" are not allowed
ERROR: multiple primary keys for table "community" are not allowed
etc.
Is there a recommended way to copy the postgres?
Thanks for your time.
_______________________________________________
Netdisco mailing list
netdisco-users@lists.sourceforge.net<mailto:netdisco-users@lists.sourceforge.net>
https://sourceforge.net/p/netdisco/mailman/netdisco-users/
--
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com/
Phone: +41 79 644 77 64
--
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com/
Phone: +41 79 644 77 64
smime.p7m
Description: smime.p7m
--- End Message ---
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users
