centos pgadmin數據庫連接方法

Prerequisites
Before connecting to a PostgreSQL database via pgAdmin on CentOS, ensure the following:

• PostgreSQL is installed and running on the target server.
• You have valid database credentials (username/password).
• Network access between your client machine and the CentOS server is permitted.

Step 1: Install pgAdmin on CentOS
pgAdmin can be installed via the official YUM repository. Run the following commands:

# Add the pgAdmin repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the default PostgreSQL module (if installed) to avoid conflicts
sudo dnf -qy module disable postgresql

# Install pgAdmin4
sudo dnf install -y pgadmin4

# Enable and start the pgAdmin web interface
sudo systemctl enable --now pgadmin4

After installation, access pgAdmin in your browser at http://<your_centos_server_ip>:80.

Step 2: Configure PostgreSQL for Remote Connections
To allow pgAdmin to connect to PostgreSQL from a remote machine, modify the PostgreSQL configuration files:

1. Edit postgresql.conf
   Locate the file (typically at /var/lib/pgsql/<version>/data/postgresql.conf or /usr/local/pgsql/data/postgresql.conf) and set:

   listen_addresses = '*'  # Allows connections from all IP addresses
   

   Save and exit.

2. Edit pg_hba.conf
   Open the file (usually in the same directory as postgresql.conf) and add the following line to permit MD5-password authentication from all IPs:

   host all all 0.0.0.0/0 md5
   

   Save and exit.

3. Restart PostgreSQL
   Apply changes by restarting the service:

   sudo systemctl restart postgresql-<version>  # Replace <version> with your PostgreSQL version (e.g., 15)
   

4. Secure the Connection (Optional but Recommended)
   For production environments, replace md5 with scram-sha-256 in pg_hba.conf and use SSL encryption. Update postgresql.conf with:

   ssl = on
   ssl_cert_file = '/path/to/server.crt'
   ssl_key_file = '/path/to/server.key'
   

Step 3: Configure Firewall Rules
Allow incoming traffic on PostgreSQL’s default port (5432) using firewalld:

sudo firewall-cmd --permanent --zone=public --add-port=5432/tcp
sudo firewall-cmd --reload

If using iptables, run:

sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
sudo service iptables save

Step 4: Add a Database Connection in pgAdmin

1. Open pgAdmin
   Launch your browser and navigate to http://<your_centos_server_ip>:80. Log in with your pgAdmin credentials (set during installation).

2. Create a New Server Connection

   • Right-click the “Servers” node in the left sidebar and select Create > Server.
   • In the “General” tab, enter a descriptive name (e.g., “Production DB”).
   • Go to the “Connection” tab and fill in the details:
     • Host: IP address or hostname of the CentOS server running PostgreSQL.
     • Port: 5432 (default PostgreSQL port).
     • Username: Your PostgreSQL username (e.g., postgres).
     • Password: The password for the specified user.
   • Click Save to store the connection.

3. Test the Connection
   Double-click the newly created server to initiate the connection. Enter the user’s password when prompted. If successful, you’ll see the server’s databases in the left sidebar.

Troubleshooting Tips

• Connection Refused: Verify PostgreSQL is running (sudo systemctl status postgresql-<version>) and the firewall allows port 5432.
• Authentication Failed: Ensure the username/password is correct and pg_hba.conf is configured to allow the client IP.
• pgAdmin Not Loading: Check if the pgAdmin service is active (sudo systemctl status pgadmin4) and the port (80) is open.