Pengenalan postgresql
PostgreSQL adalah sistem manajemen basis data (DBMS) berfitur lengkap dengan penekanan kuat pada ekstensibilitas dan kepatuhan SQL. Ini didukung oleh pengembangan sumber terbuka selama 20 tahun, dan mendukung kueri SQL (relasional) dan JSON (non-relasional).
PostgreSQL adalah salah satu database terpopuler di industri yang digunakan untuk berbagai aplikasi web, seluler, dan analitik. Sekarang mari kita lihat panduan langkah demi langkah tentang cara menginstal PostgreSQL di mesin Ubuntu 22.04, serta cara menyelesaikan pengaturan server PostgreSQL.
Cara menginstall Postgresql di Ubuntu 24.04
Follow these 9 steps to install Postgresql on Ubuntu 20.04 and connect to it through a remote server:
#Langkah 1: Menambahkan Official Repository
You may want to install PostgreSQL from an official repository, since it is updated more frequently than official Ubuntu sources.
Pertama, lakukan instalasi wget dan sertifikat ca dengan perintah berikut
sudo apt install wget ca-certificates
pentaho@server:~$ sudo apt install wget ca-certificates Reading package lists... Done Building dependency tree... Done Reading state information... Done wget is already the newest version (1.21.2-2ubuntu1). ca-certificates is already the newest version (20230311ubuntu0.22.04.1). The following packages were automatically installed and are no longer required: linux-headers-5.15.0-69 linux-headers-5.15.0-69-generic linux-headers-5.15.0-71 linux-headers-5.15.0-71-generic linux-image-5.15.0-69-generic linux-image-5.15.0-71-generic linux-modules-5.15.0-69-generic linux-modules-5.15.0-71-generic linux-modules-extra-5.15.0-69-generic linux-modules-extra-5.15.0-71-generic Use 'sudo apt autoremove' to remove them. 0 upgraded, 0 newly installed, 0 to remove and 156 not upgraded. pentaho@server:~$
Then, get the certificate, add it to apt-key management utility and create a new configuration file with an official PostgreSQL repository address inside.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
pentaho@server:~$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)). OK pentaho@server:~$
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
pentaho@server:~$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list' pentaho@server:~$
#Step 2: Install PostgreSQL
It is always a good idea to download information about all packages available for installation from your configured sources before the actual installation.
sudo apt update
pentaho@server:~$ sudo apt update Get:1 http://security.ubuntu.com/ubuntu jammy-security InRelease [110 kB] Get:2 http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease [123 kB] Get:3 http://cz.archive.ubuntu.com/ubuntu bionic InRelease [242 kB] Hit:4 http://us.archive.ubuntu.com/ubuntu jammy InRelease Get:5 http://us.archive.ubuntu.com/ubuntu jammy-updates InRelease [119 kB] Get:6 http://security.ubuntu.com/ubuntu jammy-security/main amd64 Packages [1,155 kB] Get:7 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 Packages [296 kB] Get:8 http://security.ubuntu.com/ubuntu jammy-security/main i386 Packages [403 kB] Hit:9 http://us.archive.ubuntu.com/ubuntu jammy-backports InRelease Get:10 http://security.ubuntu.com/ubuntu jammy-security/main Translation-en [212 kB] Get:11 http://security.ubuntu.com/ubuntu jammy-security/universe amd64 Packages [842 kB] Get:12 http://security.ubuntu.com/ubuntu jammy-security/universe i386 Packages [592 kB] Get:13 http://us.archive.ubuntu.com/ubuntu jammy-updates/main i386 Packages [569 kB] Get:14 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages [1,371 kB] Get:15 http://us.archive.ubuntu.com/ubuntu jammy-updates/main Translation-en [272 kB] Get:16 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 Packages [1,049 kB] Get:17 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe i386 Packages [689 kB] Fetched 8,046 kB in 6s (1,440 kB/s) Reading package lists... Done Building dependency tree... Done Reading state information... Done 164 packages can be upgraded. Run 'apt list --upgradable' to see them. W: http://apt.postgresql.org/pub/repos/apt/dists/jammy-pgdg/InRelease: Key is stored in legacy trusted.gpg keyring (/etc/apt/trusted.gpg), see the DEPRECATION section in apt-key(8) for details. N: Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease' doesn't support architecture 'i386' W: http://cz.archive.ubuntu.com/ubuntu/dists/bionic/InRelease: Key is stored in legacy trusted.gpg keyring (/etc/apt/trusted.gpg), see the DEPRECATION section in apt-key(8) for details. pentaho@server:~$
Now is the time to do the actual PostgreSQL installation. This will install the latest PostgreSQL version along with the newest extensions and additions that are not yet officially part of the PostgreSQL core.
sudo apt install postgresql postgresql-contrib
pentaho@server:~$ sudo apt install postgresql postgresql-contrib -y Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages were automatically installed and are no longer required: linux-headers-5.15.0-69 linux-headers-5.15.0-69-generic linux-headers-5.15.0-71 linux-headers-5.15.0-71-generic linux-image-5.15.0-69-generic linux-image-5.15.0-71-generic linux-modules-5.15.0-69-generic linux-modules-5.15.0-71-generic linux-modules-extra-5.15.0-69-generic linux-modules-extra-5.15.0-71-generic Use 'sudo apt autoremove' to remove them. The following additional packages will be installed: libpq5 postgresql-16 postgresql-client-16 postgresql-client-common postgresql-common Suggested packages: postgresql-doc postgresql-doc-16 The following NEW packages will be installed: postgresql-16 postgresql-client-16 postgresql-contrib The following packages will be upgraded: libpq5 postgresql postgresql-client-common postgresql-common 4 upgraded, 3 newly installed, 0 to remove and 160 not upgraded. Need to get 20.7 MB of archives. After this operation, 69.4 MB of additional disk space will be used. Get:1 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-common all 257.pgdg22.04+1 [239 kB] Get:2 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-client-common all 257.pgdg22.04+1 [94.3 kB] Get:3 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 libpq5 amd64 16.2-1.pgdg22.04+1 [214 kB] Get:4 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-client-16 amd64 16.2-1.pgdg22.04+1 [1,886 kB] Get:5 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-16 amd64 16.2-1.pgdg22.04+1 [18.1 MB] Get:6 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql all 16+257.pgdg22.04+1 [69.2 kB] Get:7 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-contrib all 16+257.pgdg22.04+1 [69.2 kB] Fetched 20.7 MB in 4s (4,662 kB/s) Preconfiguring packages ... (Reading database ... 199839 files and directories currently installed.) Preparing to unpack .../0-postgresql-common_257.pgdg22.04+1_all.deb ... Leaving 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common' Unpacking postgresql-common (257.pgdg22.04+1) over (238) ... Preparing to unpack .../1-postgresql-client-common_257.pgdg22.04+1_all.deb ... Unpacking postgresql-client-common (257.pgdg22.04+1) over (238) ... Preparing to unpack .../2-libpq5_16.2-1.pgdg22.04+1_amd64.deb ... Unpacking libpq5:amd64 (16.2-1.pgdg22.04+1) over (14.8-0ubuntu0.22.04.1) ... Selecting previously unselected package postgresql-client-16. Preparing to unpack .../3-postgresql-client-16_16.2-1.pgdg22.04+1_amd64.deb ... Unpacking postgresql-client-16 (16.2-1.pgdg22.04+1) ... Selecting previously unselected package postgresql-16. Preparing to unpack .../4-postgresql-16_16.2-1.pgdg22.04+1_amd64.deb ... Unpacking postgresql-16 (16.2-1.pgdg22.04+1) ... Preparing to unpack .../5-postgresql_16+257.pgdg22.04+1_all.deb ... Unpacking postgresql (16+257.pgdg22.04+1) over (14+238) ... Selecting previously unselected package postgresql-contrib. Preparing to unpack .../6-postgresql-contrib_16+257.pgdg22.04+1_all.deb ... Unpacking postgresql-contrib (16+257.pgdg22.04+1) ... Setting up postgresql-client-common (257.pgdg22.04+1) ... Installing new version of config file /etc/postgresql-common/supported_versions ... Setting up libpq5:amd64 (16.2-1.pgdg22.04+1) ... Setting up postgresql-common (257.pgdg22.04+1) ... Replacing config file /etc/postgresql-common/createcluster.conf with new version Merging postmaster.1.gz alternatives into psql.1.gz link group ... update-alternatives: updating alternative /usr/share/postgresql/14/man/man1/psql.1.gz because link group psql.1.gz has changed slave links update-alternatives: using /usr/share/postgresql/16/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode '/etc/apt/trusted.gpg.d/apt.postgresql.org.gpg' -> '/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg' Setting up postgresql-client-16 (16.2-1.pgdg22.04+1) ... Setting up postgresql-16 (16.2-1.pgdg22.04+1) ... Creating new PostgreSQL cluster 16/main ... /usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/main --auth-local peer --auth-host scram-sha-256 --no-instructions The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgresql/16/main ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Asia/Jakarta creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Setting up postgresql-contrib (16+257.pgdg22.04+1) ... Setting up postgresql (16+257.pgdg22.04+1) ... Processing triggers for man-db (2.10.2-1) ... Processing triggers for libc-bin (2.35-0ubuntu3.1) ... Scanning processes... Scanning linux images... Running kernel seems to be up-to-date. No services need to be restarted. No containers need to be restarted. No user sessions are running outdated binaries. No VM guests are running outdated hypervisor (qemu) binaries on this host. pentaho@server:~$
#Step 3: Melakukan pengecekan PostgreSQL status
Melakukan pengecekan status service postgresql
service postgresql status
Hasil outputnya akan seperti ini:
pentaho@server:~$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Thu 2024-02-15 18:14:06 WIB; 3s ago
Process: 1823600 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1823600 (code=exited, status=0/SUCCESS)
CPU: 3ms
Feb 15 18:14:06 server systemd[1]: Starting PostgreSQL RDBMS...
Feb 15 18:14:06 server systemd[1]: Finished PostgreSQL RDBMS.
pentaho@server:~$
#Step 4: Start Using PostgreSQL Command Line Tool
When you install PostgreSQL a default admin user “postgres” is created by the default. You must use it to log-in to your PostgreSQL database for the first time.
A “psql” command-line client tool is used to interact with the database engine. You should invoke it as a “postgres” user to start an interactive session with your local database.
sudo -u postgres psql
In addition to creating a postgres admin user for you, PostgreSQL installation also creates a default database named “postgres” and connects you to it automatically when you first launch psql.
After first launching psql, you may check the details of your connection by typing \conninfo into the interpreter.
postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". postgres=#
You are now connected to database “postgres” as user “postgres”.
If you want to see a list of all the databases that are available on a server, use \l command.
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)
postgres=#
And to see a list of all the users with their privileges use \du command.
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=#
Since the default “postgres” user does not have a password, you should set it yourself.
postgres=# \password postgres Enter new password for user "postgres": Enter it again: postgres=#
#Step 5: Create and Populate a New Database
You are now connected to your database server through psql command line tool with full access rights, so it’s time to create a new database.
postgres=# CREATE DATABASE test_mydb; CREATE DATABASE postgres=#
After the new “test_erp” database is created, connect to it.
postgres=# \c test_mydb; You are now connected to database "test_mydb" as user "postgres". test_mydb=#
Now you are ready to start creating tables where your data will be stored. Let’s create your first table with a primary key, and three client attributes.
test_mydb=# CREATE TABLE clients (id SERIAL PRIMARY KEY, first_name VARCHAR, last_name VARCHAR, role VARCHAR); CREATE TABLE test_mydb=#
You may double check that your new table is created successfully by typing a \dt command.
test_mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | clients | table | postgres
(1 row)
test_mydb=#
Let’s now insert the first row into your newly created “clients” table.
test_mydb=# INSERT INTO clients (first_name, last_name, role) VALUES ('John', 'Smith', 'CEO');
INSERT 0 1
test_mydb=#
And query the table to get all its rows.
test_mydb=# SELECT * FROM clients; id | first_name | last_name | role ----+------------+-----------+------ 1 | John | Smith | CEO (1 row) test_mydb=#
As you can see, John Smith has been successfully added to the “clients” table of the “test_erp” database.
#Step 6: Setup PostgreSQL server
It’s fun to play with the database locally, but eventually you will need to connect to it through a remote server.
When you install a PostgreSQL server, it is only accessible locally through the loopback IP address of your machine. However, you may change this setting in the PostgreSQL configuration file to allow remote access.
Let’s now exit the interactive psql session by typing exit, and access postgresql.conf configuration file of PostgreSQL version 14 by using vim text editor.
vim /etc/postgresql/14/main/postgresql.conf
Uncomment and edit the listen_addresses attribute to start listening to start listening to all available IP addresses.
listen_addresses = '*'
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' #listen_addresses = 'localhost' # what IP address(es) to listen on;
Now edit the PostgreSQL access policy configuration file.
vim /etc/postgresql/14/main/pg_hba.conf
Append a new connection policy (a pattern stands for [CONNECTION_TYPE][DATABASE][USER] [ADDRESS][METHOD]) in the bottom of the file.
host all all 0.0.0.0/0 md5
We are allowing TCP/IP connections (host) to all databases (all) for all users (all) with any IPv4 address (0.0.0.0/0) using an MD5 encrypted password for authentication (md5).
It is now time to restart your PostgreSQL service to load your configuration changes.
systemctl restart postgresql
And make sure your system is listening to the 5432 port that is reserved for PostgreSQL.
pentaho@uworkshop-VirtualBox:~$ ss -nlt | grep 5432 LISTEN 0 200 127.0.0.1:5432 0.0.0.0:* pentaho@uworkshop-VirtualBox:~$
If everything is OK, you should see this output.
#Step 7: Connect to PostgreSQL database through a remote host
Your PostgreSQL server is now running and listening for external requests. It is now time to connect to your database through a remote host.
#Step 7.1: Connect via Command Line Tool
A psql command line tool also allows you to connect to a remote database. If you don’t have it on your remote machine yet, follow the steps 1 – 3 for a full PostgreSQL installation or install a command line tool only by using sudo apt install postgresql-client command.
You may now connect to a remote database by using the following command pattern:
psql -h [ip address] -p [port] -d [database] -U [username]
Let’s now connect to a remote PostgreSQL database that we have hosted on one of the Cherry Servers machines.
psql -h 5.199.162.56 -p 5432 -d test_erp -U postgres
To double check your connection details use the \conninfo command.
Now you can start writing SQL queries to retrieve data from your database tables.
SELECT * FROM clients;
We can see that our previously created entry is safely stored in the “clients” table.
#Step 8: Connect via Application Code
To connect to a database through your application code you need a database driver that allows you to connect to a specific database from your chosen programming language.
If you are using Python, a standard PostgreSQL driver is psycopg2. Let’s install this library using pip packet manager.
pip install psycopg2-binary
You can now import psycopg2 into your code and start using PostgreSQL natively.
import psycopg2
# Connect to your PostgreSQL database on a remote server
conn = psycopg2.connect(host="5.199.162.56", port="5432", dbname="test_erp", user="postgres", password="test123")
# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a test query
cur.execute("SELECT * FROM clients")
# Retrieve query results
records = cur.fetchall()
# Finally, you may print the output to the console or use it anyway you like
print(records)
You will get the following output when using an ipython3 interpreter:
Discover how MKC Limited leveraged Cherry Servers’ secure cloud infrastructure to enhance data security, reduce cost, and increase system efficiency.
#Step 9: Connect via GUI Client
Although there are many GUI clients that can help you connect to a database and manage it, pgAdmin is probably the most popular option for PostgreSQL, and we highly recommend using it.
After installing pgAdmin 4 and running it you will get to a standard pgAdmin 4 dashboard.
Press Add New Server button and enter the information of your remote server.
After saving your credentials you will be automatically connected to a remote database server.
You may now open a Query Tool for your selected database test_erp and start writing your queries.
#Conclusion
Congrats! You have successfully installed a PostgreSQL database, set-up a database server and started interacting with it through a remote machine. It is now time to dive deeper into the official PostgreSQL documentation to build your application.