Here is a comprehensive, step-by-step guide to migrating JFrog Artifactory 7.x from the default Derby database to PostgreSQL, specifically addressing all the real-world issues you’ve encountered during the process.

Step-by-Step Guide: Migrating Artifactory 7 from Derby to PostgreSQL (with Troubleshooting)

1. Prerequisites

• Full Backup: Back up your entire $JFROG_HOME directory and ensure you can restore your system if anything goes wrong.
• PostgreSQL Server: Should be installed, reachable, and you have superuser credentials.
• PostgreSQL JDBC Driver: Download the latest .jar and place it in the correct Artifactory directory if needed.

2. Prepare PostgreSQL and Artifactory User

a. Create the Database and User

Log in to PostgreSQL as a superuser and run:

CREATE USER artifactory WITH PASSWORD 'YourStrongPassword';
CREATE DATABASE artifactory WITH OWNER = artifactory ENCODING='UTF8';
GRANT ALL PRIVILEGES ON DATABASE artifactory TO artifactory;

b. Assign Schema Privileges

Switch to the artifactory database and grant required privileges:

sql\c artifactory
GRANT ALL ON SCHEMA public TO artifactory;
ALTER SCHEMA public OWNER TO artifactory;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO artifactory;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO artifactory;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO artifactory;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO artifactory;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO artifactory;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO artifactory;

3. Configure PostgreSQL Networking

• Enable remote access if Artifactory and PostgreSQL are on different hosts:
  Edit postgresql.conf (set listen_addresses = '*' or hostname), and update pg_hba.conf to allow connections from Artifactory’s IP like: texthost artifactory artifactory x.x.x.x/32 md5
• Reload PostgreSQL after editing configuration: textsudo systemctl reload postgresql

4. System Export from Derby Installation

From the Artifactory UI (while still using Derby):

• Navigate to:
  Administration → Import & Export → System Export
• Select Exclude Content if you only want to transfer database contents (quicker).
• Export to a stable, accessible path and note the directory.

5. Stop Artifactory

Gracefully stop Artifactory before changing database configuration:

text$JFROG_HOME/artifactory/app/bin/artifactory.sh stop

6. Switch Artifactory to Use PostgreSQL

a. Edit system.yaml

Edit:

textshared:
  database:
    type: postgresql
    driver: org.postgresql.Driver
    url: "jdbc:postgresql://<your-host>:5432/artifactory"
    username: artifactory
    password: YourStrongPassword

Check for:

• No tabs, only spaces (YAML is space-sensitive).
• Ensure all other config sections (e.g. security, node) are maps, not strings.

b. Place PostgreSQL JDBC Driver

Copy the PostgreSQL JDBC .jar into:

text$JFROG_HOME/artifactory/var/bootstrap/artifactory/tomcat/lib

7. Start Artifactory with New DB

text$JFROG_HOME/artifactory/app/bin/artifactory.sh start

Watch logs:
If you see connection refusal or pg_hba errors, revisit previous steps.

Permission denied on schema public?
Repeat the privilege grants in Step 2b.

8. Do System Import

• Once Artifactory is fully started with the empty PostgreSQL DB, log in.
• Go to:
  Administration → Import & Export → System Import
• Select the export directory you created earlier.
• Start the import and wait for completion.

9. Final Validation and Restart

• Confirm all repositories, users, permissions, metadata, and settings are present.
• Test uploading/downloading artifacts.
• If all looks good, do a final restart: text$JFROG_HOME/artifactory/app/bin/artifactory.sh restart

10. Troubleshooting Real-World Issues

• YAML Validation failed: Ensure all sections are maps/dictionaries, not strings or null.
• pg_hba.conf errors: Make sure you have a line for Artifactory’s IP. Reload PostgreSQL after editing.
• “Permission denied for schema public”: Run all SQL privilege/ownership commands in Step 2b as PostgreSQL superuser.
• Connection refused: Check PostgreSQL is running on the expected host/port, firewall rules, and PostgreSQL’s listen settings.
• Containers: For Docker setups, use container network names, not localhost.

11. Post-migration Checks

• Verify storage paths for binaries (they stay on disk).
• Re-configure integrations/plugins if needed.
• Monitor Artifactory logs for hidden migration issues.

Following the above, you’ll migrate from Derby to PostgreSQL in Artifactory 7, handling all common configuration and privilege pitfalls with minimal downtime.