Debian如何使用pgAdmin管理PostgreSQL数据库

Installing pgAdmin on Debian
To manage PostgreSQL databases with pgAdmin on Debian, start by updating your system’s package list to ensure you have the latest version information:

sudo apt update

Install pgAdmin 4 (the latest stable version) using the APT package manager:

sudo apt install pgadmin4

During installation, you’ll be prompted to create a master password—this is essential for encrypting saved server connections and sensitive data. Set a strong password and remember it, as you’ll need it to access pgAdmin later.

Configuring PostgreSQL for pgAdmin
pgAdmin requires a dedicated PostgreSQL user and database for authentication. Switch to the postgres user (PostgreSQL’s default superuser):

sudo su - postgres

Open the PostgreSQL interactive terminal (psql):

psql

Create a dedicated user for pgAdmin (replace pgadmin_user and your_password with your preferred credentials):

CREATE USER pgadmin_user WITH PASSWORD 'your_password';

Create a database owned by this user (e.g., pgadmin_db):

CREATE DATABASE pgadmin_db OWNER pgadmin_user;

Grant all privileges on the database to the user:

GRANT ALL PRIVILEGES ON DATABASE pgadmin_db TO pgadmin_user;

Exit psql and the postgres user session:

\q
exit

Starting and Accessing pgAdmin
Launch the pgAdmin service:

sudo systemctl start pgadmin4

Set pgAdmin to start automatically on system boot:

sudo systemctl enable pgadmin4

Configure the firewall to allow access to pgAdmin’s default port (5050) using ufw (Uncomplicated Firewall):

sudo ufw allow 5050/tcp
sudo ufw enable

Access pgAdmin in your web browser by navigating to http://your_server_ip:5050. Log in using the master email address and password you set during installation.

Connecting to a PostgreSQL Server via pgAdmin
Once logged into pgAdmin, expand the left-hand navigation pane and right-click the Servers node. Select Create > Server.
In the General tab, enter a descriptive name for the server (e.g., “Local PostgreSQL” or “Remote DB Server”).
Switch to the Connection tab and fill in the server details:

• Host: Enter the server’s IP address (e.g., 192.168.1.100) or localhost for local connections.
• Port: Use the default PostgreSQL port 5432 (unless you’ve changed it in the postgresql.conf file).
• Maintenance database: Select postgres (the default system database).
• Username: Enter the username you created earlier (e.g., pgadmin_user).
• Password: Enter the password for this user.
  Click Save to store the connection. If configured correctly, you’ll see the server listed under Servers. Double-click it to connect—pgAdmin will retrieve the database list and display it in the left-hand pane.

Managing Databases and Tables
With the server connected, you can manage PostgreSQL databases and tables directly from pgAdmin:

• Create a Database: Right-click the server, select Create > Database, enter a name, and configure settings (e.g., owner, encoding).
• Modify Table Structure: Expand the server > database > Schemas > public > Tables node. Right-click a table and select Properties to modify columns, constraints, or indexes.
• Execute SQL Queries: Right-click the server, select Query Tool, and write SQL statements (e.g., SELECT * FROM table_name;). Use syntax highlighting and code folding to improve efficiency.