postgresql如何与ubuntu集成

Installing PostgreSQL on Ubuntu
To integrate PostgreSQL with Ubuntu, the first step is installing PostgreSQL and its additional tools. Open a terminal and run:

sudo apt update && sudo apt install postgresql postgresql-contrib -y

• postgresql: The core database server.
• postgresql-contrib: Provides extra utilities (e.g., pg_dump, pg_restore) for advanced operations.
  After installation, the PostgreSQL service starts automatically. Verify its status with:

sudo systemctl status postgresql

A message like “active (running)” confirms successful startup.

Accessing PostgreSQL
Ubuntu configures PostgreSQL to use the postgres system user by default. Switch to this user and enter the PostgreSQL command-line interface (CLI):

sudo -u postgres psql

The prompt changes to postgres=#, indicating you’re logged into the PostgreSQL shell. Here, you can run SQL commands (e.g., \l to list databases, \q to quit).

Basic Database Operations
Common tasks include creating users, databases, and granting permissions:

• Change the postgres user’s password:

  ALTER USER postgres WITH ENCRYPTED PASSWORD 'your_secure_password';
  

• Create a new user and database:

  CREATE USER myuser WITH PASSWORD 'user_password';
  CREATE DATABASE mydb OWNER myuser;
  

• Grant privileges:

  GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
  

These commands set up a dedicated user and database for your application, following the principle of least privilege.

Configuring Remote Access (Optional)
If you need to access PostgreSQL from another machine, modify two key configuration files:

1. Edit postgresql.conf:
   Change the listen_addresses parameter to allow connections from all IPs (or a specific subnet):

   sudo nano /etc/postgresql/<version>/main/postgresql.conf
   

   Find the line #listen_addresses = 'localhost' and replace it with:

   listen_addresses = '*'
   

2. Edit pg_hba.conf:
   Add a rule to allow password-authenticated remote connections (replace 0.0.0.0/0 with a specific IP range for security):

   sudo nano /etc/postgresql/<version>/main/pg_hba.conf
   

   Append this line at the end:

   host all all 0.0.0.0/0 scram-sha-256
   

3. Restart PostgreSQL and configure the firewall:

   sudo systemctl restart postgresql
   sudo ufw allow 5432/tcp
   sudo ufw reload
   

Replace <version> with your installed PostgreSQL version (e.g., 16 for PostgreSQL 16).

Securing Your Setup

• Use strong passwords: Always set complex passwords for the postgres user and any new users.
• Restrict remote access: Limit pg_hba.conf entries to trusted IPs instead of 0.0.0.0/0 in production.
• Keep PostgreSQL updated: Regularly run sudo apt update && sudo apt upgrade to patch security vulnerabilities.

Verifying the Integration
Test the setup by connecting to PostgreSQL from the local machine or a remote client:

psql -h localhost -U myuser -d mydb

Enter the password for myuser when prompted. A successful connection displays the PostgreSQL prompt (mydb=#), confirming that PostgreSQL is fully integrated with Ubuntu and ready for use.