One place for hosting & domains


      How To Set Up Continuous Archiving and Perform Point-In-Time-Recovery with PostgreSQL 12 on Ubuntu 20.04

      The author selected the Diversity in Tech Fund to receive a donation as part of the Write for DOnations program.


      PostgreSQL is a widely used relational database that supports ACID transactions. The acronym ACID stands for atomicity, consistency, isolation, and durability. These are four key properties of database transactions that PostgreSQL supports to ensure the persistence and validity of data in the database.

      One method PostgreSQL uses to maintain ACID properties is Write-Ahead Logging (WAL). PostgreSQL first records any transaction on the database to the WAL log files before it writes the changes to the database cluster’s data files.

      With continuous archiving, the WAL files are copied to secondary storage, which has a couple of benefits. For example, a secondary database cluster can use the archived WAL file for replication purposes, but you can also use the files to perform point-in-time-recovery (PITR). That is, you can use the files to rollback a database cluster to a desirable point if an accident happens.

      In this tutorial, you will set up continuous archiving with a PostgreSQL 12 cluster on Ubuntu 20.04 and perform PITR on the cluster.


      To complete this tutorial, you’ll need the following:

      Step 1 — Configuring Continuous Archiving on the Database Cluster

      In this first step, you need to configure your PostgreSQL 12 cluster to archive the cluster’s WAL files in a directory different from the cluster’s data directory. To do this, you must first create a new directory somewhere to archive the WAL files.

      Create a new directory as follows:

      You now need to give the default PostgreSQL user, postgres, permission to write to this directory. You can achieve this by changing the ownership of the directory using the chown command:

      • sudo chown postgres:postgres database_archive

      Now that you have a directory set up for the cluster to archive the WAL files into, you must enable archiving in the postgresql.conf configuration file, which you can find in the /etc/postgresql/12/main/ directory by default.

      Open the configuration file with your text editor:

      • sudo nano /etc/postgresql/12/main/postgresql.conf

      Once you have opened the file, you’ll uncomment the line with the archive_mode variable on it by removing the # from the start of the line. Also, change the value of archive_mode to on like the following:


      . . .
      archive_mode = on
      . . .

      You’ll also specify the command the cluster uses to archive the files. PostgreSQL provides an archive command that will work for this tutorial, which you can read about in the official PostgreSQL docs. Uncomment the archive_command variable and add the following command:


      . . .
      archive_command = 'test ! -f /path/to/database_archive/%f && cp %p /path/to/database_archive/%f'
      . . .

      The archive command here first checks to see if the WAL file already exists in the archive, and if it doesn’t, it copies the WAL file to the archive.

      Replace the /path/to/database_archive with the path to the database_archive directory you created earlier. For example, if you created this in your home directory: ~/database_archive.

      Lastly, you need to configure the wal_level variable. wal_level dictates how much information PostgreSQL writes to the log. For continuous archiving, this needs to be set to at least replica:


      . . .
      #wal_level = replica
      . . .

      This is already the default value in PostgreSQL 12, so you shouldn’t need to change it, but it is something to remember if you ever go to change this variable.

      You can now save and exit your file.

      To implement the changes to your database cluster configuration file, you need to restart the cluster as follows:

      • sudo systemctl restart postgresql@12-main

      If PostgreSQL restarts successfully, the cluster will archive every WAL file once it is full. By default, each WAL file is 16MB.

      In the case that you need to archive a transaction immediately, you can force the database cluster to change and archive the current WAL file by running the following command on the cluster:

      • sudo -u postgres psql -c "SELECT pg_switch_wal();"

      With the database cluster successfully copying the WAL files to the archive, you can now perform a physical backup of the database cluster’s data files.

      Step 2 — Performing a Physical Backup of the PostgreSQL Cluster

      It is important to take regular backups of your database to help mitigate data loss should the worst happen. PostgreSQL allows you to take both logical and physical backups of the database cluster. However, for PITR, you need to take a physical backup of the database cluster. That is, you need to make a copy of all the database’s files in PostgreSQL’s data directory. By default, the PostgreSQL 12 data directory is /var/lib/postgresql/12/main/.

      Note: You can also find the location of the data directory by running the following command on the cluster:

      • sudo -u postgres psql -c "SHOW data_directory;"

      In the previous step, you made the directory, database_archive, to store all the archived WAL files. In this step you need to create another directory, called database_backup, to store the physical backup you will take.

      Once again, make the directory:

      Now ensure that the postgres user has permission to write to the directory by changing the ownership:

      • sudo chown postgres:postgres database_backup

      Now that you have a directory for the backup, you need to perform a physical backup of the database cluster’s data files. Fortunately, PostgreSQL has the built-in pg_basebackup command that performs everything for you. Run the command as the postgres user:

      • sudo -u postgres pg_basebackup -D /path/to/database_backup

      Replace /path/to/ with the path to your directory.

      With this physical backup of the database cluster, you are now able to perform point-in-time-recovery on the cluster.

      Step 3 — Performing Point-In-Time-Recovery on the Database Cluster

      Now that you have at least one physical backup of the database and you’re archiving the WAL files, you can now perform PITR, if you need to rollback the database to a previous state.

      First, if the database is still running, you’ll need to shut it down. You can do this by running the systemctl stop command:

      • sudo systemctl stop postgresql@12-main

      Once the database is no longer running, you need to remove all the files in PostgreSQL’s data directory. But first, you need to move the pg_wal directory to a different place as this might contain unarchived WAL files that are important for recovery. Use the mv command to move the pg_wal directory as follows:

      • sudo mv /var/lib/postgresql/12/main/pg_wal ~/

      Now, you can remove the /var/lib/postgresql/12/main directory entirely and recreate it as such:

      • sudo rm -rf /var/lib/postgresql/12/main

      Followed by:

      • sudo mkdir /var/lib/postgresql/12/main

      Now, you need to copy all the files from the physical backup you made in the previous step to the new empty data directory. You can do this with cp:

      • sudo cp -a /path/to/database_backup/. /var/lib/postgresql/12/main/

      You also need to ensure the data directory has the postgres user as the owner and the appropriate permissions. Run the following command to change the owner:

      • sudo chown postgres:postgres /var/lib/postgresql/12/main

      And update the permissions:

      • sudo chmod 700 /var/lib/postgresql/12/main

      The WAL files in the pg_wal directory copied from the physical backup are outdated and not useful. You need to replace them with the WAL files in the pg_wal directory that you copied before you emptied out the PostgreSQL’s data directory as some of the files might not have been archived before stopping the server.

      Remove the pg_wal file in the /var/lib/postgresql/12/main directory as follows:

      • sudo rm -rf /var/lib/postgresql/12/main/pg_wal

      Now copy the files from the pg_wal directory you saved before clearing out the data directory:

      • sudo cp -a ~/pg_wal /var/lib/postgresql/12/main/pg_wal

      With the data directory restored correctly, you need to configure the recovery settings to ensure the database server recovers the archived WAL files correctly. The recovery settings are found in the postgresql.conf configuration file in the /etc/postgresql/12/main/ directory.

      Open the configuration file:

      • sudo nano /etc/postgresql/12/main/postgresql.conf

      Once you have the file open, locate the restore_command variable and remove the # character from the start of the line. Just like you did with archive_command in the first step, you need to specify how PostgreSQL should recover the WAL files. Since the archive command just copies the files to the archive, the restore command will copy the files back. The restore_command variable will be similar to the following:


      . . .
      restore_command = 'cp /path/to/database_archive/%f %p'
      . . .

      Remember to replace /path/to/database_archive/ with the path to your archive directory.

      Next, you have the option to specify a recovery target. This is the point that the database cluster will try to recover to before leaving recovery mode. The recovery target can be a timestamp, transaction ID, log sequence number, the name of a restore point created with the pg_create_restore_point() command, or whenever the database reaches a consistent state. If no recovery target is specified, the database cluster will read through the entire log of WAL files in the archive.

      For a complete list of options for the recovery_target variable, consult the official PostgreSQL documentation.

      Note: The recovery target must be a point in time after the physical backup you are using was taken. If you need to return to an earlier point, then you need to use an earlier backup of the database.

      Once you have set restore_command and recovery_target, save and exit the file.

      Before restarting the database cluster, you need to inform PostgreSQL that it should start in recovery mode. You can achieve this by creating an empty file in the cluster’s data directory called recovery.signal. To create an empty file in the directory, use the touch command:

      • sudo touch /var/lib/postgresql/12/main/recovery.signal

      Now you can restart the database cluster by running:

      • sudo systemctl start postgresql@12-main

      If the database started successfully, it will enter recovery mode. Once the database cluster reaches the recovery target, it will remove the recovery.signal file.

      Now that you have successfully recovered your database cluster to the desired state, you can begin your normal database operations. If you want to recover to a different point in time, you can repeat this step.


      In this tutorial, you set up a PostgreSQL 12 database cluster to archive WAL files and then you used the archived WAL files to perform point-in-time-recovery. You can now rollback a database cluster to a desirable state if an accident happens.

      To learn more about continuous archiving and point-in-time-recovery, you can read the docs.

      For more tutorials on PostgreSQL, check out our PostgreSQL topic page.

      Source link

      What is PostgreSQL?

      PostgreSQL, commonly referred to as “Postgres,” is an open-source relational database management system. As with other relational databases, PostgreSQL stores data in tables made up of rows and columns. Users can define, manipulate, control, and query data using Structured Query Language, more commonly known as SQL. PostgreSQL bills itself as “the most advanced open-source relational database in the world,” and emphasizes extensibility and compliance with the SQL standard.

      For more educational resources related to PostgreSQL, please visit:

      A complete list of our educational resources on PostgreSQL can be found on our PostgreSQL tag page.

      Source link

      Cara Menginstal dan Menggunakan PostgreSQL pada Ubuntu 20.04


      Sistem manajemen basis data relasional adalah komponen penting dari banyak situs web dan aplikasi. Sistem ini menyediakan cara yang terstruktur untuk menyimpan, mengelola, dan mengakses informasi.

      PostgreSQL, atau Postgres, adalah sistem manajemen basis data relasional yang memberikan penerapan dari bahasa kueri SQL. Sistem ini sesuai dengan standar dan memiliki banyak fitur lanjutan seperti transaksi yang andal dan konkurensi tanpa kunci baca.

      Panduan ini mendemonstrasikan cara menginstal Postgres pada server Ubuntu 20.04. Panduan ini juga menyediakan beberapa instruksi untuk administrasi basis data umum.


      Untuk mengikuti tutorial ini, Anda akan membutuhkan satu server Ubuntu 20.04 yang telah dikonfigurasi dengan mengikuti Panduan Penyiapan Server Awal untuk Ubuntu 20.04 dari kami. Setelah menyelesaikan tutorial prasyarat ini, server Anda akan memiliki pengguna non-root dengan izin sudo dan firewall dasar.

      Langkah 1 — Menginstal PostgreSQL

      Repositori asali Ubuntu memuat paket Postgres, sehingga Anda dapat menginstal ini menggunakan sistem paket apt.

      Jika Anda belum melakukan itu baru-baru ini, segarkan indeks paket lokal server Anda:

      Lalu, instal paket Postgres bersama dengan paket -contrib yang menambah beberapa utilitas dan fungsionalitas tambahan:

      • sudo apt install postgresql postgresql-contrib

      Karena kini perangkat lunak sudah terinstal, kita dapat memeriksa cara kerjanya dan kemungkinan perbedaannya dengan sistem manajemen basis data relasional lain yang Anda mungkin gunakan.

      Langkah 2 — Menggunakan Peran dan Basis Data PostgreSQL

      Secara asali, Postgres menggunakan konsep yang disebut “role” untuk menangani autentikasi dan otorisasi. Dalam beberapa hal, ini mirip dengan akun bergaya Unix reguler, tetapi Postgres tidak membedakan antara pengguna dan grup, serta alih-alih lebih memilih istilah “peran” yang fleksibel.

      Setelah instalasi, Postgres sudah siap untuk menggunakan autentikasi ident, yang berarti ini mengasosiasikan peran-peran Postgres dengan akun sistem Unix/Linux yang cocok. Jika peran ada di dalam Postgres, nama pengguna Unix/Linux dengan nama yang sama dapat masuk sebagai peran itu.

      Prosedur instalasi menciptakan akun pengguna bernama postgres yang terasosiasi dengan peran Postgres asali. Untuk menggunakan Postgres, Anda dapat log masuk ke akun itu.

      Ada beberapa cara untuk memanfaatkan akun ini guna mengakses Postgres.

      Beralih ke Akun postgres

      Beralihlah ke akun postgres pada server Anda dengan mengetik:

      Anda kini dapat segera mengakses prompt PostgreSQL dengan mengetik:

      Dari sana, Anda bebas berinteraksi dengan sistem manajemen basis data sesuai kebutuhan.

      Keluarlah dari prompt PostgreSQL dengan mengetik:

      Ini akan membawa Anda kembali ke prompt perintah Linux postgres.

      Mengakses Prompt Postgres Tanpa Beralih Akun

      Anda juga dapat menjalankan perintah yang Anda inginkan secara langsung menggunakan akun postgres dengan sudo.

      Misalnya, pada contoh terakhir, Anda diperintahkan untuk pergi ke prompt Postgres dengan pertama-tama beralih ke pengguna postgres, lalu menjalankan psql untuk membuka prompt Postgres. Anda dapat melakukan ini dalam satu langkah dengan menjalankan perintah tunggal psql sebagai pengguna postgres dengan sudo, seperti ini:

      Ini akan membawa Anda log masuk secara langsung ke Postgres tanpa shell bash perantara di antaranya.

      Sekali lagi, Anda dapat keluar dari sesi Postgres interaktif dengan mengetik:

      Banyak kasus penggunaan membutuhkan lebih dari satu peran Postgres. Lanjutkan membaca mempelajari cara mengonfigurasi ini.

      Langkah 3 — Menciptakan Peran Baru

      Saat ini, Anda baru saja mengonfigurasi peran postgres di dalam basis data. Anda dapat membuat peran baru dari baris perintah dengan perintah createrole. Bendera --interactive akan meminta nama peran baru ini kepada Anda serta menanyakan apakah bendera itu harus memiliki izin superuser.

      Jika Anda log masuk sebagai akun postgres, Anda dapat membuat pengguna baru dengan mengetik:

      Jika Anda lebih memilih untuk menggunakan sudo untuk setiap perintah tanpa berpindah dari akun normal Anda, ketik:

      • sudo -u postgres createuser --interactive

      Skrip akan memberi Anda beberapa pilihan dan, berdasarkan respons Anda, menjalankan perintah Postgres yang benar untuk menciptakan pengguna sesuai dengan spesifikasi Anda.


      Enter name of role to add: sammy Shall the new role be a superuser? (y/n) y

      Anda dapat memiliki kontrol lebih banyak dengan memberi beberapa bendera tambahan. Periksa opsi dengan membuka laman man:

      Instalasi Postgres Anda kini memiliki pengguna baru, tetapi Anda belum menambahkan basis data apa pun. Bagian selanjutnya menggambarkan proses ini.

      Langkah 4 — Menciptakan Basis Data Baru

      Asumsi lain yang dibuat oleh sistem autentikasi Postgres secara asali adalah bahwa pada setiap peran yang digunakan untuk log masuk akan memiliki basis data bernama sama yang dapat diakses oleh peran itu.

      Ini berarti bahwa jika pengguna yang Anda ciptakan di bagian terakhir bernama sammy, maka peran itu akan mencoba terhubung ke basis data yang juga bernama “sammy” secara asali. Anda dapat menciptakan basis data yang sesuai dengan perintah createdb.

      Jika Anda log masuk sebagai akun postgres, Anda dapat mengetik sesuatu seperti:

      Jika Anda lebih memilih untuk menggunakan sudo untuk setiap perintah tanpa berpindah dari akun normal, Anda dapat mengetik:

      • sudo -u postgres createdb sammy

      Fleksibilitas ini menyediakan beberapa jalur untuk menciptakan basis data sesuai kebutuhan.

      Langkah 5 — Membuka Prompt Postgres dengan Peran Baru

      Untuk log masuk dengan autentikasi berbasis ident, Anda akan membutuhkan pengguna Linux dengan nama yang sama dengan peran dan basis data Postgres.

      Jika tidak ada pengguna Linux yang cocok, Anda dapat menciptakannya dengan perintah adduser. Anda harus melakukan ini dari akun non-root dengan privilese sudo (yang berarti, tidak log masuk sebagai pengguna postgres):

      Setelah akun baru ini tersedia, Anda dapat beralih dan terhubung ke basis data itu dengan mengetik:

      Atau, Anda dapat melakukannya dalam baris ini:

      Perintah ini akan membuat Anda log masuk secara otomatis, dengan asumsi bahwa semua komponen telah dikonfigurasi dengan benar.

      Jika Anda ingin pengguna terhubung ke basis data yang berbeda, Anda dapat melakukannya dengan menetapkan basis datanya seperti ini:

      Setelah log masuk, Anda dapat memeriksa informasi koneksi saat ini dengan mengetik:


      You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

      Ini berguna jika Anda terhubung ke basis data non-asali atau menggunakan pengguna non-asali.

      Langkah 6 — Membuat dan Menghapus Tabel

      Karena kini Anda mengetahui cara terhubung ke sistem basis data PostgreSQL, Anda dapat mempelajari beberapa tugas manajemen Postgres dasar.

      Sintaks dasar untuk membuat tabel adalah sebagai berikut:

      CREATE TABLE table_name (
          column_name1 col_type (field_length) column_constraints,
          column_name2 col_type (field_length),
          column_name3 col_type (field_length)

      Seperti yang Anda lihat, perintah ini memberi nama pada tabel, lalu mendefinisikan kolom serta jenis kolom dan panjang maksimum dari data bidang. Anda juga dapat menambahkan batasan tabel pada masing-masing kolom.

      Anda dapat mempelajari lebih lanjut tentang cara membuat dan mengelola tabel di Postgres di sini.

      Untuk demonstrasi, buat tabel berikut:

      • CREATE TABLE playground (
      • equip_id serial PRIMARY KEY,
      • type varchar (50) NOT NULL,
      • color varchar (25) NOT NULL,
      • location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
      • install_date date
      • );

      Perintah ini akan membuat tabel yang menginventarisasi peralatan taman bermain. Kolom pertama di dalam tabel akan menampung nomor ID peralatan dari tipe serial, yang merupakan bilangan bulat yang bertambah secara otomatis. Kolom ini juga memiliki batasan dari PRIMARY KEY yang berarti bahwa nilai di dalamnya harus unik dan bukan nol.

      Dua baris selanjutnya membuat kolom untuk type (jenis) peralatan dan color (warna) secara berurutan, yang tidak boleh kosong. Baris setelah baris ini membuat kolom location (lokasi) serta batasan yang mengharuskan nilai itu menjadi salah satu dari delapan kemungkinan nilai. Baris terakhir membuat kolom date (tanggal) yang merekam tanggal saat Anda memasang peralatan itu.

      Untuk dua kolom (equip_id dan install_date) adalah perintah tidak menentukan panjang bidang. Alasan untuk ini adalah bahwa beberapa tipe data tidak memerlukan panjang rangkaian karena panjang atau format sudah tersirat.

      Anda dapat melihat tabel baru Anda dengan mengetik:


      List of relations Schema | Name | Type | Owner --------+-------------------------+----------+------- public | playground | table | sammy public | playground_equip_id_seq | sequence | sammy (2 rows)

      Tabel taman bermain Anda ada di sini, tetapi juga ada sesuatu yang bernama playground_equip_id_seq yang merupakan sequence (urutan) tipe. Ini adalah representasi dari tipe serial yang Anda berikan pada kolom equip_id. Ini melacak angka selanjutnya pada urutan dan dibuat secara otomatis untuk kolom tipe ini.

      Jika Anda hanya ingin melihat tabel tanpa urutan, Anda dapat mengetik:


      List of relations Schema | Name | Type | Owner --------+------------+-------+------- public | playground | table | sammy (1 row)

      Setelah tabel siap, mari kita gunakan untuk berlatih mengelola data.

      Langkah 7 — Menambahkan, Membuat Kueri, dan Menghapus Data pada Tabel

      Karena Anda sudah memiliki tabel, Anda dapat memasukkan beberapa data ke dalamnya. Sebagai contoh, tambahkan perosotan dan ayunan dengan memanggil tabel yang Anda ingin tambahkan, menamai kolom, lalu memberi data untuk masing-masing kolom, seperti ini:

      • INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
      • INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

      Anda harus berhati-hati saat memasukkan data untuk menghindari beberapa masalah umum. Misalnya, jangan membungkus nama kolom dengan tanda kutip, tetapi nilai kolom yang Anda masukkan memang membutuhkan tanda kutip.

      Hal lain yang harus diingat adalah bahwa Anda tidak memasukkan nilai untuk kolom equip_id. Karena nilai ini secara otomatis dihasilkan setiap kali Anda menambah baris baru ke tabel.

      Ambil informasi yang Anda tambahkan dengan mengetik:

      • SELECT * FROM playground;


      equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 1 | slide | blue | south | 2017-04-28 2 | swing | yellow | northwest | 2018-08-16 (2 rows)

      Di sini, Anda dapat melihat bahwa equip_id telah berhasil diisi dan semua data yang lain telah terorganisir dengan benar.

      Jika perosotan di taman bermain rusak dan Anda harus menghapusnya, Anda juga dapat menghapus baris dari tabel dengan mengetik:

      • DELETE FROM playground WHERE type="slide";

      Buat kueri tabel lagi:

      • SELECT * FROM playground;


      equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 2 | swing | yellow | northwest | 2018-08-16 (1 row)

      Perhatikan bahwa baris slide (perosotan) tidak lagi menjadi bagian dari tabel.

      Langkah 8 — Menambahkan dan Menghapus Kolom dari Tabel

      Setelah membuat tabel, Anda dapat mengubahnya dengan menambah atau menghapus kolom. Tambahkan kolom untuk menunjukkan kunjungan pemeliharaan terakhir pada setiap peralatan dengan mengetik:

      • ALTER TABLE playground ADD last_maint date;

      Jika Anda melihat informasi tabel Anda lagi, Anda akan melihat kolom baru telah ditambahkan, tetapi tidak ada data yang dimasukkan:

      • SELECT * FROM playground;


      equip_id | type | color | location | install_date | last_maint ----------+-------+--------+-----------+--------------+------------ 2 | swing | yellow | northwest | 2018-08-16 | (1 row)

      Jika Anda menemukan bahwa kru pekerja menggunakan alat berbeda untuk melacak riwayat pemeliharaan, Anda dapat menghapusnya dari kolom dengan mengetik:

      • ALTER TABLE playground DROP last_maint;

      Ini menghapus kolom last_maint dan nilai apa pun yang ditemukan di dalamnya, tetapi tidak mengubah semua data lainnya.

      Langkah 9 — Memperbarui Data pada Tabel

      Sejauh ini, Anda telah mempelajari cara menambahkan catatan ke tabel dan cara menghapusnya, tetapi tutorial ini belum membahas cara untuk mengubah entri yang ada.

      Anda dapat memperbarui nilai-nilai dari entri yang sudah ada dengan meminta catatan yang Anda inginkan dan mengatur kolom pada nilai yang Anda ingin gunakan. Anda dapat meminta catatan swing (ini akan sesuai dengan setiap ayunan di tabel Anda) dan mengubah warnanya menjadi red (merah). Ini akan berguna jika Anda mengecat ayunan:

      • UPDATE playground SET color="red" WHERE type="swing";

      Anda dapat memverifikasi bahwa operasi berhasil dengan meminta data lagi:

      • SELECT * FROM playground;


      equip_id | type | color | location | install_date ----------+-------+-------+-----------+-------------- 2 | swing | red | northwest | 2018-08-16 (1 row)

      Seperti yang Anda lihat, perosotan kini sudah terdaftar sebagai berwarna merah.


      Anda sekarang sudah siap dengan PostgreSQL di server Ubuntu 20.04. Jika Anda ingin mempelajari lebih lanjut tentang Postgres dan cara menggunakannya, kami menyarankan Anda untuk membaca panduan berikut:

      Source link