Backup and Restore PostgreSQL Database

PostgreSQL has been shipped with a lot of tools for ease of managing the database. Some actions that we typically perform in managing databases are backing up a database and restoring it to another database server. In PostgreSQL, we can utilize pg_dump to back up the data from a database and psql or pg_restore to restore the data into a new database.

Generating A Backup File

There are several available formats for the exported data: plain, directory, compressed, and custom. The plain format is using plain SQL syntax to export the data. To make the tables are exported separately, we can utilize directory format. The custom format is utilizing a built-in compression mechanism and results in an optimized binary formatted file. It is suitable for a large database. If we use a custom format, we can only restore it using pg_restore, but we have the ability to selectively choose desired tables to be restored.

The following command is used to generate a plain formatted file with the INSERT syntax as the SQL command for inserting records. The file parameter is used to specify the destination file to store the result.

pg_dump \
  --host=localhost \
  --port=5432 \
  --username=postgres \
  --password \
  --dbname=mydatabase
  --file=outputFilePlain \
  --format=p \
  --inserts \
  --no-owner \
  --no-privileges

The following command is used to generate a custom formatted file.

pg_dump \
  --host=localhost \
  --port=5432 \
  --username=postgres \
  --password \
  --dbname=mydatabase
  --file=outputFileCustom \
  --format=c \
  --inserts \
  --no-owner \
  --no-privileges

Restoring Database from A Backup File

As I have mentioned above, we can utilize psql or pg_restore to restore the database from a backup file. But, only the pg_restore command can be used to restore the custom formatted file.

The following command is used to restore a database from a plain formatted file. We must create the destination database first before running this command. The file parameter is used to specify the source file.

psql \
  --host=localhost \
  --port=5432 \
  --username=postgres \
  --password \
  --dbname=mynewdatabase \
  --file=inputFilePlain

The following command is used to restore the database from a custom formatted file using pg_restore. We need to notice that the last part is the name of the input file without the name of a command parameter.

pg_restore \
  --host=localhost \
  --port=5432 \
  --username=postgres \
  --password \
  --dbname=mynewdatabase \
  inputFileCustom

Comments

Configuring Swap Memory on Ubuntu Using Ansible

If we maintain a Linux machine with a low memory capacity while we are required to run an application with high memory consumption, enabling swap memory is an option. Ansible can be utilized as a helper tool to automate the creation of swap memory. A swap file can be allocated in the available storage of the machine. The swap file then can be assigned as a swap memory. Firstly, we should prepare the inventory file. The following snippet is an example, you must provide your own configuration. [server] 192.168.1.2 [server:vars] ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa Secondly, we need to prepare the task file that contains not only the tasks but also some variables and connection information. For instance, we set /swapfile as the name of our swap file. We also set the swap memory size to 2GB and the swappiness level to 60. - hosts: server become: true vars: swap_vars: size: 2G swappiness: 60 For simplicity, we only check the...

Rangkaian Sensor Infrared dengan Photo Dioda

Keunggulan photodioda dibandingkan LDR adalah photodioda lebih tidak rentan terhadap noise karena hanya menerima sinar infrared, sedangkan LDR menerima seluruh cahaya yang ada termasuk infrared. Rangkaian yang akan kita gunakan adalah seperti gambar di bawah ini. Pada saat intensitas Infrared yang diterima Photodiode besar maka tahanan Photodiode menjadi kecil, sedangkan jika intensitas Infrared yang diterima Photodiode kecil maka tahanan yang dimiliki photodiode besar. Jika tahanan photodiode kecil maka tegangan V- akan kecil . Misal tahanan photodiode mengecil menjadi 10kOhm. Maka dengan teorema pembagi tegangan: V- = Rrx/(Rrx + R2) x Vcc V- = 10 / (10+10) x Vcc V- = (1/2) x 5 Volt V- = 2.5 Volt Sedangkan jika tahanan photodiode besar maka tegangan V- akan besar (mendekati nilai Vcc). Misal tahanan photodiode menjadi 150kOhm. Maka dengan teorema pembagi tegangan: V- = Rrx/(Rrx + R2) x Vcc V- = 150 / (1...

API Gateway Using KrakenD

The increasing demands of users for high-quality web services create the need to integrate various technologies into our application. This will cause the code base to grow larger, making maintenance more difficult over time. A microservices approach offers a solution, where the application is built by combining multiple smaller services, each with a distinct function. For example, one service handles authentication, another manages business functions, another maintains file uploads, and so on. These services communicate and integrate through a common channel. On the client side, users don't need to understand how the application is built or how it functions internally. They simply send a request to a single endpoint, and processes like authentication, caching, or database querying happen seamlessly. This is where an API gateway is effective. It handles user requests and directs them to the appropriate handler. There are several tools available for building an API gateway, su...

Deploying a Web Server on UpCloud using Terraform Modules

In my earlier post , I shared an example of deploying UpCloud infrastructure using Terraform from scratch. In this post, I want to share how to deploy the infrastructure using available Terraform modules to speed up the set-up process, especially for common use cases like preparing a web server. For instance, our need is to deploy a website with some conditions as follows. The website can be accessed through HTTPS. If the request is HTTP, it will be redirected to HTTPS. There are 2 domains, web1.yourdomain.com and web2.yourdomain.com . But, users should be redirected to "web2" if they are visiting "web1". There are 4 main modules that we need to set up the environment. Private network. It allows the load balancer to connect with the server and pass the traffic. Server. It is used to host the website. Load balancer. It includes backend and frontend configuration. Dynamic certificate. It is requ...

Configure Gitlab SMTP Setting

Gitlab CE or EE is shipped with the capability to send messages through SMTP service as the basic feature to send notifications or updates to the users. The configuration parameters are available in /etc/gitlab/gitlab.rb . Each SMTP service provider has a different configuration, therefore the Gitlab configuration parameters should be adjusted according to the requirements. Some examples have been provided by Gitlab here . This is an example if you use the Zoho service. gitlab_rails['smtp_enable'] = true gitlab_rails['smtp_address'] = "smtp.zoho.com" gitlab_rails['smtp_port'] = 587 gitlab_rails['smtp_authentication'] = "plain" gitlab_rails['smtp_enable_starttls_auto'] = true gitlab_rails['smtp_user_name'] = "gitlab@mydomain.com" gitlab_rails['smtp_password'] = "mypassword" gitlab_rails['smtp_domain'] = "smtp.zoho.com" This is another example of using Amazon SES w...

Running CI/CD Pipeline with GitLab CI

GitLab allows us to deploy CI/CD pipeline runners on our own resources within our environment. This option is available not only for the self-hosted plan but also for the cloud service plan (gitlab.com). With this setup, unlike GitHub Action, we can avoid incurring additional costs for extended pipeline runtime. This is because we can deploy the runner on an on-demand server and optimize its usage. GitLab CI offers several options for setting up resources to run CI/CD pipelines. A runner can be configured to handle jobs for specific groups or projects using designated tags. It can also be set to use different executors, such as Shell, Docker, Kubernetes, or VirtualBox. A comparison table of the supported executors is available in the executor documentation . Some executors offer greater flexibility and ease of use, while others may be more rigid but enhance server security. Installing the runner in our machine For example, we will deploy the runner on an Ubuntu serve...

LUKI NOTES

Search This Blog