Database Tips - netdisco/netdisco GitHub Wiki
Netdisco’s database uses PostgreSQL. It has multiple tables but they are not connected with each other via foreign key constraints. Therefore there is duplicated information.
While older versions will mostly work but there will be subtle breakage which is hard to troubleshoot. A warning is displayed in System Information tab if you’re running a risky version.
# change to database user
netdisco$ sudo su - postgres
# check if you really are postgres
postgres$ whoami
# this should return "postgres". if it returns something
# else see the note under this code fragment.
# launch postgres client
postgres$ psql
# connect to netdisco DB
\c netdisconot all linux distros allow the postgres user to log in. if sudo su - postgres followed
by whoami is not returning "postgres" there are several options.
-
your distro does not allow an interactive login for postgres
-
either use psql under any user with the arguments "-W" "-d" "-U" (check psql manual for that).
-
use
sudo -u postgres psql
-
-
your distro uses another user to run postgres
-
consult your distro’s manual
-
if you get failure to authenticate you will most likely be using unix sockets under a user without
the correct access. fixing this is not that hard but out of scope here, but will most likely
involve editing pg_hba.conf and/or using other connection strings with psql.
~/bin/netdisco-do psqlreset and if will be fine again).
SELECT * FROM pg_catalog.pg_tables where tableowner = 'netdisco';Please see the query here, which is what Netdisco uses to draw its neighbors map: https://github.com/netdisco/netdisco/blob/master/lib/App/Netdisco/DB/Result/Virtual/DeviceLinks.pm
Sometimes you will see "connection refused" or "database is unversioned" in the logs while starting the docker instances.
This is cosmetic, do not worry. It happens because the database takes a while to start and the web and backend daemons are complaining. They will later connect.
Ignore the error if Netdisco seems to be functioning correctly.
The default maximum query string postgresql will show in it’s pg_stat_activity
view is 1024 byte. this will not suffice for several of our queries since they
can contain a lot of whitespace. raising this to 8192 or higher is recommended
for troubleshooting. do note that this is a database wide settings and you’ll need
to restart postgresql after making this change. if your database only contains
netdisco this should not result in a performance impact.
SELECT pid, age(clock_timestamp(), query_start), usename, query, wait_event_type, wait_event, state
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;this shows all non-idle transactions, what they’re executing and, if they’re waiting, for something, what they’re waiting on. should you leave out the idle filter you might see a lot of connections waiting on clientread, this just means there is an active connection and postgresql is waiting for the client’s next command.
Due to the database usage pattern of Netdisco, the database might encounter "index bloat": indexes consisting of very sparsely populated pages that use up a lot of space. This mostly occurs in the node and node_ip tables, and autovacuum nor manualy vacuuming frees up the space. Instead, it can be necessary to perform a REINDEX operation.
To find affected tables, use this query. Problematic tables will have high ibloat and wastedibytes values. To rewrite the indexes of an affected table, use e.g. netdisco-do psql -e 'reindex table concurrently node_ip'.
It’s also an option to reindex the whole database: netdisco-do psql -e 'reindex database concurrently netdisco'
Some users reported large amounts of entries in these two tables, sometimes to an extent that interferes with polling or available disk space.
Both admin and device_skip only contain temporary data, and can be reinitialized like so:
$ sudo systemctl stop netdisco-backend
$ netdisco-do psql
netdisco=> truncate table admin;
TRUNCATE TABLE
netdisco=> truncate table device_skip;
TRUNCATE TABLE
netdisco=> reindex table admin;
REINDEX
netdisco=> reindex table device_skip;
REINDEX
$ sudo systemctl start netdisco-backend
Due to the high churn (turnover) on the database, PostgreSQL might not have chance to auto-vacuum and reindex efficiently.
You can help by adding something like this in cron once a week:
reindex table concurrently node; reindex table concurrently node_ip;
docker-compose exec netdisco-postgresql psql -U postgres
and then in there
alter role netdisco password 'your new password';
We recommend you backup the Netdisco database regularly. You could put the
following commands into a shell script and call it nightly from cron:
DATE=`date +%Y%m%d`
/usr/bin/pg_dump -F c --create -f /path/to/backups/netdisco-pgsql-$DATE.dump netdisco
gzip -9f /path/to/backups/netdisco-pgsql-$DATE.dump
/usr/bin/find /path/to/backups/ -type f -ctime +30 -exec rm {} \;
This will keep 30 days of backups. You don’t need to stop Netdisco during the backup.
truncate device;
truncate device_ip;
truncate device_module;# First stop netdisco
netdisco$ ~/bin/netdisco-web stop
netdisco$ ~/bin/netdisco-backend stop
# Connect as postgres
netdisco$ sudo su - postgres
# Delete DB
postgres$ dropdb netdisco
# Create DB
postgres$ createdb -O netdisco netdisco
# Return to netdisco user
postgres$ exit
# Init DB
netdisco$ ~/bin/netdisco-deploy
# start netdisco again
netdisco$ ~/bin/netdisco-web start
netdisco$ ~/bin/netdisco-backend start