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//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//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 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.

以上就是关于“postgresql如何与ubuntu集成”的相关介绍，筋斗云是国内较早的云主机应用的服务商，拥有10余年行业经验，提供丰富的云服务器、租用服务器等相关产品服务。云服务器资源弹性伸缩，主机vCPU、内存性能强悍、超高I/O速度、故障秒级恢复；电子化备案，提交快速，专业团队7×24小时服务支持！

简单好用、高性价比云服务器租用链接：https://www.jindouyun.cn/product/cvm