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

Increase of Malicious Activities and Implementation of reCaptcha

In recent time, I've seen the increase of malicious activities such as login attempts or phishing emails to some accounts I manage. Let me list some of them and the actions taken. SSH Access Attempts This happened on a server that host a Gitlab server. Because of this case, I started to limit the incoming traffic to the server using internal and cloud firewall provided by the cloud provider. I limit the exposed ports, connected network interfaces, and allowed protocols. Phishing Attempts This typically happened through email and messaging platform such as Whatsapp and Facebook Page messaging. The malicious actors tried to share a suspicious link lured as invoice, support ticket, or something else. Malicious links shared Spammy Bot The actors leverage one of public endpoint on my website to send emails. Actually, the emails won't be forwarded anywhere except to my own email so this just full my inbox. This bot is quite active, but I'm still not sure what...

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...

Deliver SaaS According Twelve-Factor App

If you haven't heard of the twelve-factor app , it gives us a recommendation or a methodology for developing SaaS or web apps structured into twelve items. The recommendation has some connections with microservice architecture and cloud-native environments which become more popular today. We can learn the details on its website . In this post, we will do a quick review of the twelve points. One Codebase Multiple Deployment We should maintain only one codebase for our application even though the application may be deployed into multiple environments like development, staging, and production. Having multiple codebases will lead to any kinds of complicated issues. Explicitly State Dependencies All the dependencies for running our application should be stated in the project itself. Many programming languages have a kind of file that maintains a list of the dependencies like package.json in Node.js. We should also be aware of the dependencies related to the pla...

Kenshin VS The Assassin

It is an assassin versus assassin.

Handling PDF Generation in Web Service

If we are building a website that requires a PDF generation feature, there are several options for implementing it based on the use cases or user requirements. First, we can generate the PDF on the client side using any available client library. It is suitable if the use case is to print out some data that is already available inside certain website components, and we want to maintain the styles of the components in the document. Second, we can do it fully in the back-end using any library available, such as PDF-lib, jsPDF, and so on. This approach is suitable if we want to keep the data processing or any related business functions in the back-end server. This second approach might have disadvantages, such as the difficulty of maintaining the design assets and styles which are already on our website. Third, it is using a hybrid approach, where certain processes are handled on the client side, and some are handled on the back-end. In this post, I want to discuss more about the...

Free Cloud Services from UpCloud

Although I typically deploy my development environment or experimental services on UpCloud , I do not always stay updated on its announcements. Recently, I discovered that UpCloud has introduced a new plan called the Essentials plan, which enables certain cloud services to be deployed at no cost. The complimentary services are generally associated with network components or serve as the foundation for other cloud services. This feature is particularly useful when retaining foundational services, such as a load balancer, is necessary, while tearing down all services and reconfiguring the DNS and other application settings each time we temporarily clean up infrastructure to reduce costs is undesirable. When reviewing the service specifications of the cloud services in the Essentials plan, they appear to be very similar to those in the Development plan. The difference in service levels is unclear, but it could be related to hardware or resource allocation. For instance, the loa...

LUKI NOTES

Search This Blog