Skip to main content

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

Popular posts from this blog

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 / (150+10) x Vcc V- = (150/160) x 5

Rangkaian Sensor Cahaya dengan LDR

LDR(Light Depending Resistor) adalah resistor yang nilai hambatannya bergantung dari intensitas cahaya yang ia terima. Jika intensitas cahaya rendah (gelap) maka nilai resistansinya akan menjadi sangat besar (mencapai 1MOhm atau lebih), sedangkan jika intensitas cahaya tinggi (terang) nilai resistansinya menjadi kecil (mencapai 10kOhm atau kurang). Sifat ini dapat kita pergunakan dalam rangkaian sensor cahaya. Misalkan jika kita menginginkan sensor cahaya yang akan menyalakan lampu indikasi ketika ada cahaya dan mematikan lampu indikasi ketika tidak ada cahaya. Kita dapat menggunakan rangkaian seperti gambar di bawah ini. Transistor NPN berfungsi sebagai gate. Arus dari kolektor akan mengalir menuju emitor jika arus dari base besar namun jika arus pada base kecil maka arus dari kolektor tidak akan menuju emitor. Pada rangkaian sensor cahaya dengan LDR, ketika intensitas cahaya tinggi (terang) maka arus dari VCC akan melewati LDR kemudian melewati RESISTOR dan masuk ke

Installing APCu in PHP 7

APCu is one of caching application for PHP. In this case, I use PHP 7.0 on Ubuntu 16.04. In PHP 7.0, this application is provided via PEAR. First, install PEAR. $ sudo apt-get install php-pear Install APCu. If an error occured state that there's no phpize, you need to install PHP 7.0-dev which provide phpize support. $ sudo apt-get install php7.0-dev $ sudo pecl install apcu Create APCu module configuration in PHP modules directory. $ sudo echo "extension = apcu.so" >> /etc/php/7.0/mods-available/apcu.ini Add that configuration to PHP FPM and CLI. $ sudo ln -s /etc/php/7.0/mods-available/apcu.ini /etc/php/7.0/fpm/conf.d/30-apcu.ini $ sudo ln -s /etc/php/7.0/mods-available/apcu.ini /etc/php/7.0/cli/conf.d/30-apcu.ini Restart PHP FPM.

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 exi

Setting Up Next.js Project With ESLint, Typescript, and AirBnB Configuration

If we initiate a Next.js project using the  create-next-app tool, our project will be included with ESLint configuration that we can apply using yarn run lint . By default, the tool installs eslint-config-next and extends next/core-web-vitals in the ESLint configuration. The Next.js configuration has been integrated with linting rules for React and several other libraries and tools. yarn create next-app --typescript For additional configuration such as AirBnB, it is also possible. First, we need to install the peer dependencies of eslint-config-airbnb . We also add support for Typescript using eslint-config-airbnb-typescript . yarn add --dev eslint-config-airbnb eslint-plugin-import eslint-plugin-jsx-a11y eslint-plugin-react eslint-plugin-react-hooks yarn add --dev eslint-config-airbnb-typescript @typescript-eslint/eslint-plugin @typescript-eslint/parser After that, we can update the .eslintrc.json file for the new configuration. { "extends": [ "airb

Managing MongoDB Records Using NestJS and Mongoose

NestJS is a framework for developing Node.js-based applications. It provides an additional abstraction layer on top of Express or other HTTP handlers and gives developers a stable foundation to build applications with structured procedures. Meanwhile, Mongoose is a schema modeling helper based on Node.js for MongoDB. There are several main steps to be performed for allowing our program to handle MongoDB records. First, we need to add the dependencies which are @nestjs/mongoose , mongoose , and @types/mongoose . Then, we need to define the connection configuration on the application module decorator. import { MongooseModule } from '@nestjs/mongoose'; @Module({ imports: [ MongooseModule.forRoot('mongodb://localhost:27017/mydb'), ], controllers: [AppController], providers: [AppService], }) Next, we create the schema definition using helpers provided by NestJS and Mongoose. The following snippet is an example with a declaration of index setting and an o