1. Introduction
PostgreSQL is a highly stable and high-performance relational database widely used in various applications and systems on Ubuntu. This article provides a clear, step-by-step guide on installing and configuring PostgreSQL on Ubuntu. It is designed for beginners, explaining each stage in detail and including instructions for verifying the installation and troubleshooting connection errors, so you can set up your environment with confidence.
2. Prerequisites and Preparation
First, ensure that your Ubuntu version is either 20.04 or 22.04. Before installing PostgreSQL, update your package list to retrieve the latest package information.
sudo apt updateThis step helps ensure a smooth installation process.
3. Installing PostgreSQL
3.1 Adding the PostgreSQL Repository
The default Ubuntu repository may not always include the latest version of PostgreSQL. To install the latest version, add the official PostgreSQL repository.
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc3.2 Installing PostgreSQL
After adding the repository, install PostgreSQL and additional tools using the following command:
sudo apt update
sudo apt install postgresql postgresql-contrib3.3 Verifying the Installation
Once the installation is complete, check the PostgreSQL version to ensure it was installed correctly.
postgres --version
4. Initial Configuration
4.1 Setting Up the PostgreSQL User
When PostgreSQL is installed, a system user named postgres is automatically created. Switch to this user to manage the database with the following command:
sudo -i -u postgres4.2 Editing Local Connection Settings
Modify the pg_hba.conf file to configure authentication settings. By default, only local connections are allowed. To enable remote connections, edit the following file:
sudo nano /etc/postgresql/14/main/pg_hba.confFor example, you can enhance security by specifying md5 authentication as follows:
local all postgres md5
host all all 127.0.0.1/32 md5After making changes, restart the PostgreSQL service to apply the new settings:
sudo systemctl restart postgresql5. Quick Functionality Check
5.1 Starting and Stopping PostgreSQL
PostgreSQL starts automatically after installation. However, you can manually start, stop, or check the service status using the following commands:
sudo systemctl status postgresql
sudo systemctl start postgresql
sudo systemctl stop postgresql5.2 Checking the Database
Use the psql command to connect to PostgreSQL and check the list of available databases.
sudo -u postgres psqlOnce inside the PostgreSQL prompt, type \l to display a list of databases.
6. Installing and Configuring pgAdmin (Optional)
pgAdmin is a GUI tool that makes managing PostgreSQL easier. Install it using the following command:
sudo apt install pgadmin4After installation, open a browser and access http://localhost/pgadmin to start managing PostgreSQL through the GUI.
7. Troubleshooting and Common Errors
7.1 Installation and Repository Errors
If you encounter dependency or repository errors during installation, verify that the repository URL is correct and update the package list again.
sudo apt update7.2 Fixing Connection Errors
If you get an error like “password authentication failed” when connecting to PostgreSQL, check your pg_hba.conf settings and verify your password. Restart the PostgreSQL service after making changes.
sudo systemctl restart postgresql7.3 Resolving Network Issues
If remote connections are not working, check the postgresql.conf file to ensure that listen_addresses is not set to “localhost”. To allow remote connections, modify the setting as follows:
sudo nano /etc/postgresql/14/main/postgresql.confChange the setting to:
listen_addresses = '*'After making this change, restart the PostgreSQL service to apply the update.
sudo systemctl restart postgresql
8. Conclusion
In this article, we covered how to install PostgreSQL on Ubuntu, perform initial setup, and verify its operation. We also discussed using pgAdmin, enabling remote connections, and troubleshooting common errors. By following these steps, even beginners can confidently set up and manage their PostgreSQL environment.
MySQLとPostgreSQLの違いを解説。性能、拡張性、ユースケースの観点から、プロジェクトに最適なデータベースを選…
