pgmigrate.md

Migrating a PostgreSQL database from version 15 to version 16 for NetBox v4 involves upgrading the PostgreSQL instance while ensuring the NetBox data remains intact. PostgreSQL 16 introduces improvements like better performance, new monitoring features, and some syntax changes, but it’s a minor version upgrade, so the process is relatively straightforward. Below is a step-by-step guide to migrate your NetBox v4 database from PostgreSQL 15 to PostgreSQL 16. Prerequisites Current Setup: NetBox v4 running with PostgreSQL 15. Target Setup: A system where PostgreSQL 16 will be installed (this can be the same server or a different one). Backup: Always back up your database before starting. Downtime: Plan for downtime, as NetBox will be unavailable during the migration. Compatibility: NetBox v4 supports PostgreSQL 16 (as of March 07, 2025, based on current trends and NetBox’s requirements), but verify this with the latest NetBox documentation if you’re using a newer release. Steps to Migrate PostgreSQL 15 to PostgreSQL 16

1. Backup the PostgreSQL 15 Database Stop NetBox services to ensure data consistency: bash sudo systemctl stop netbox netbox-rq Use pg_dump to create a backup of the NetBox database (assuming it’s named netbox): bash pg_dump -U netbox -h localhost -F c -b -v -f netbox_backup.dump netbox -F c: Custom format for flexibility (allows selective restore if needed). -b: Include large objects (blobs). -v: Verbose output for debugging. Replace -U netbox with your PostgreSQL username if different. This creates a netbox_backup.dump file.
2. Check for Deprecation Warnings in PostgreSQL 15 Before migrating, ensure there are no deprecated features in use that might break in PostgreSQL 16. Run: bash psql -U netbox -d netbox -c "SHOW server_version;" psql -U netbox -d netbox -c "SELECT * FROM pg_stat_activity;" Review logs (/var/log/postgresql/ or similar) for warnings about deprecated syntax or features. PostgreSQL 16 has minor changes (e.g., stricter handling of some functions), but NetBox’s schema is unlikely to be affected. If unsure, test the backup on a staging PostgreSQL 16 instance first.
3. Install PostgreSQL 16 Same Server Upgrade: Add the PostgreSQL 16 repository (example for Ubuntu/Debian): bash sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt update sudo apt install postgresql-16 For RHEL/CentOS: bash sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %rhel)-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo dnf install postgresql16-server New Server: Install PostgreSQL 16 on the target node following the same steps.
4. Migrate the Data There are two primary methods: pg_upgrade (faster, in-place) or dump/restore (safer, works across servers). Choose based on your setup. Option 1: Using pg_upgrade (Same Server) Stop the PostgreSQL 15 service: bash sudo systemctl stop postgresql Initialize a new PostgreSQL 16 data directory: bash sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/main Run pg_upgrade to migrate data from 15 to 16: bash sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade
   --old-datadir /var/lib/postgresql/15/main
   --new-datadir /var/lib/postgresql/16/main
   --old-bindir /usr/lib/postgresql/15/bin
   --new-bindir /usr/lib/postgresql/16/bin Start PostgreSQL 16: bash sudo systemctl start postgresql@16-main Update the system to use PostgreSQL 16 by default (adjust service files if needed): bash sudo systemctl disable postgresql@15-main sudo systemctl enable postgresql@16-main Verify: bash psql -U postgres -c "SHOW server_version;" Should show 16.x. Option 2: Using Dump/Restore (Same or Different Server) On the target server (with PostgreSQL 16 installed), create the netbox database and user: bash sudo -u postgres psql -c "CREATE DATABASE netbox;" sudo -u postgres psql -c "CREATE USER netbox WITH PASSWORD 'your_password';" sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE netbox TO netbox;" Restore the backup: bash pg_restore -U netbox -h localhost -d netbox --verbose netbox_backup.dump If moving to a new server, transfer the dump file first: bash scp netbox_backup.dump user@new-server:/path/to/dump/
5. Verify the Database Connect to PostgreSQL 16 and check the NetBox tables: bash psql -U netbox -d netbox -c "\dt" Ensure all expected tables (e.g., dcim_device, ipam_ipaddress) are present.
6. Update NetBox Configuration If migrating to a new server, update the NetBox configuration file (/opt/netbox/netbox/netbox/configuration.py) with the new PostgreSQL host details: python DATABASE = { 'NAME': 'netbox', 'USER': 'netbox', 'PASSWORD': 'your_password', 'HOST': 'new-server-hostname-or-ip', # Update if different 'PORT': '', } If on the same server, no changes are typically needed unless the port or socket changed.
7. Test NetBox Start NetBox services: bash sudo systemctl start netbox netbox-rq Run migrations (unlikely to be needed for a minor version bump, but good practice): bash cd /opt/netbox source venv/bin/activate python3 manage.py migrate Access the NetBox web interface and verify functionality (e.g., view devices, IP addresses).
8. Clean Up Once confirmed working, remove the PostgreSQL 15 package (if on the same server): bash sudo apt remove postgresql-15 # Debian/Ubuntu sudo dnf remove postgresql15 # RHEL/CentOS Delete the old data directory (/var/lib/postgresql/15/main) after verifying the migration. Notes Downtime: The pg_upgrade method is faster (minutes) but requires the same server, while dump/restore is slower (depends on database size) but works across servers. PostgreSQL 16 Changes: Review the PostgreSQL 16 release notes for breaking changes. NetBox’s schema is unlikely to need adjustments, but custom extensions or queries might. Redis: If your NetBox setup uses Redis (for caching/queuing), no changes are needed unless the Redis instance is also moving. Let me know if you encounter issues or need help with a specific step!