[EC2] move datas to production database from development database [Rails]

check extensions

Several problem may be happened by extensions between development and production environment.
So check extensions and fix the differences if you need.

production_database=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+------------+------------+------------------------------
 plpgsql | 1.0        | pg_catalog | PL/pgSQL procedural language
(1 行)


development_database=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

Dump tables

Dump from database of development environment.
You need to add --data-only option because making table schema is done by migrations.

This is the command to dump book table.

$ pg_dump --data-only -U user --table books -d development_database > tmp/data_only_books.sql

Send sql files to remote server of production environment by any method you want to use.

I use scp command.

https://ogutip.mixh.jp/wp/wp-admin/post.php?post=142&action=edit

Import SQL

Check these points

  • Exists production database
  • made table schema [by migration]

Edit SQL files

Open SQL file by vim, and comment out following line.

  • SET lock_timeout = 0;
  • SET idle_in_transaction_session_timeout = 0;
  • SET row_security = off;

These variables cause any errors like this:

ERROR:  unrecognized configuration parameter "lock_timeout"

This is mine case.
I removed 6, 10, 16 line.

  1 -- 
  2 -- PostgreSQL database dump
  3 -- 
  4    
  5 -- Dumped from database version 10.7 (Ubuntu 10.7-0ubuntu0.18.04.1)
  6 -- Dumped by pg_dump version 10.7 (Ubuntu 10.7-0ubuntu0.18.04.1)
  7    
  8 SET statement_timeout = 0;
  9 --SET lock_timeout = 0;
 10 --SET idle_in_transaction_session_timeout = 0;
 11 SET client_encoding = 'UTF8';
 12 SET standard_conforming_strings = on;
 13 SELECT pg_catalog.set_config('search_path', '', false);
 14 SET check_function_bodies = false;
 15 SET client_min_messages = warning;
 16 --SET row_security = off;     

Import

Import SQL files like this:
(You don’t have to add “-v ON_ERROR_STOP=1”)

$ psql -v ON_ERROR_STOP=1 -f data_only_books.sql -U user -d production_database
SET
SET
SET
 set_config 
------------
 
(1 行)

SET
SET
 setval 
--------
   1866
(1 行)

Check the data

$ rails c --sandbox

irb(main):001:0> Book.count
   (1.0ms)  SELECT COUNT(*) FROM "books"
=> 1503

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です