PostgreSQL Installation and Configuration Guide on Ubuntu
Installing PostgreSQL on Ubuntu: A Complete Guide
PostgreSQL, a powerful open-source relational database, is widely used on Ubuntu servers for its robustness and scalability. This guide covers the steps to install PostgreSQL on Ubuntu, manage databases and users, and secure your installation.
Introduction to PostgreSQL on Ubuntu:
Ubuntu, as one of the most popular Linux distributions, provides a highly compatible environment for PostgreSQL. Installing PostgreSQL on Ubuntu enables you to run and manage databases with high efficiency, making it suitable for various applications from small development setups to large production environments.
Steps to Install PostgreSQL on Ubuntu
This guide will walk you through installing PostgreSQL on Ubuntu, configuring it, and performing some basic commands.
Step 1: Update the Package List
First, update the Ubuntu package repository list to ensure that you’re downloading the latest version:
sudo apt update
Step 2: Install PostgreSQL
After updating, install PostgreSQL and the necessary PostgreSQL client packages:
sudo apt install postgresql postgresql-contrib
This command installs PostgreSQL along with additional utilities that are commonly used for managing the database.
Step 3: Start and Enable PostgreSQL Service
Once installed, enable the PostgreSQL service to start on boot and start it immediately:
# Start PostgreSQL service sudo systemctl start postgresql # Enable PostgreSQL to start at boot sudo systemctl enable postgresql
Step 4: Verify the Installation
To confirm PostgreSQL is running, use the following command:
sudo systemctl status postgresql
This command shows the status of PostgreSQL, including whether it is active.
Basic Configuration of PostgreSQL on Ubuntu
Once PostgreSQL is running, you can configure basic settings and set up your first database and user.
Switch to the PostgreSQL User
The default PostgreSQL installation creates a postgres user with superuser privileges. Switch to this user to manage databases:
sudo -i -u postgres
Create a New Database and User
Create a Database:
createdb mydatabase
Create a New User:
createuser --interactive
You'll be prompted to set options like superuser status for the new user.
Grant Privileges:
After creating the user, grant privileges on your new database:
psql -c "GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;"
Replace mydatabase and myuser with your actual database name and username.
Example: Connecting to PostgreSQL
To connect to PostgreSQL as a specific user, use the psql command:
psql -d mydatabase -U myuser
Once inside the psql shell, you can execute SQL commands directly:
Code:
-- Create a sample table
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
-- Insert sample data
INSERT INTO customers (name, email) VALUES (‘Jeff Tresha’, '[email protected]');
Securing PostgreSQL on Ubuntu
To secure your PostgreSQL instance, configure the following:
1. Edit the pg_hba.conf File: This file controls client authentication. Open it:
Code:
sudo nano /etc/postgresql/<version>/main/pg_hba.conf
2. Enable Password Authentication: Change peer to md5 for local connections.
3. Restart PostgreSQL:
Code:
sudo systemctl restart postgresql
This setup enhances security by requiring passwords for user connections.
Summary:
With PostgreSQL installed on Ubuntu, you’re ready to start managing databases, adding users, and securing your database environment. PostgreSQL on Ubuntu is a flexible, reliable option for developing robust database-driven applications.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/install-postgresql-on-ubuntu.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics