Перенос БД PostgreSQL 10.1 в AWS RDS PostgreSQL 10.5.

CREATE DATABASE l_generator;

CREATE USER l_generator WITH ENCRYPTED PASSWORD 'superpassword';
GRANT ALL ON database l_generator to l_generator;

pg_dump -o -h 127.0.0.1 -U postgres_migrate -d l_generator -n public -s > public_schema_l_generator.dump

psql -h database.eu-west-1.rds.amazonaws.com -U l_generator -d l_generator -f public_schema_l_generator.dump

CREATE INDEX
psql:public_schema_l_generator.dump:2168: ERROR:  data type integer has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
psql:public_schema_l_generator.dump:2175: ERROR:  data type integer has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

CREATE INDEX ads_ids_idx ON ads USING gin (ads_ids);
CREATE INDEX ads_per_run_id_ids_idx ON ads USING gin (project_run_id, ads_ids);
CREATE INDEX ads_per_run_id_ids_idx_1 ON ads USING gin (ads_ids, project_run_id);

CREATE EXTENSION btree_gin;
CREATE EXTENSION btree_gist;

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

apt install python-psycopg2

wget http://pgfoundry.org/frs/download.php/3232/skytools-2.1.13.tar.gz
tar zxf skytools-2.x.x.tar.gz
cd skytools-2.x.x

$ sudo find / -type f -name "pg_config"
/usr/bin/pg_config

./configure --prefix=/opt/skytools-2.1.13 --with-pgconfig=/usr/bin/pg_config

sudo ln -s /usr/include/postgresql/10/server/common/keywords.h /usr/include/postgresql/10/server/parser/keywords.h

pg83 = $(shell [ $( echo -e "$PGVER\n8.3"|sort -gt. -k1 -k2|head -n1 ) = $PGVER ] && echo false || echo true)
pg82 = $(shell test $(PGVER) "<" "8.2" && echo "false" || echo "true")  

sed -ie '/^pg83/s:shell test.*$:shell [ $( echo -e "$PGVER\\n8.3"|sort -gt. -k1 -k2|head -n1 ) = $PGVER ] \&\& echo false || echo true):' Makefile
sed -ie '/^pg82/s:shell test.*$:shell [ $( echo -e "$PGVER\\n8.2"|sort -gt. -k1 -k2|head -n1 ) = $PGVER ] \&\& echo false || echo true):' Makefile

#include "access/htup_details.h"

$ sudo apt install postgresql-server-dev-10
$ make 

$ sudo mkdir /usr/share/doc/postgresql-doc-10
$ sudo checkinstall make install

$ python setup.py build
$ sudo python setup.py install

$ cat ticker.ini
[pgqadm]
job_name = ticker_for_l_generator
db = dbname=l_generator host=127.0.0.1 port=5432 user=postgres_migrate password=xxxxxxxxx

# how often to run maintenance [seconds]
maint_delay = 600

# how often to check for activity [seconds]
loop_delay = 0.1
logfile = log/%(job_name)s.log
pidfile = run/%(job_name)s.pid

$ pgqadm.py ticker.ini install
$ pgqadm.py ticker.ini ticker -d

$ cat londiste.ini
[londiste]
job_name = londiste_l_genertor_migrate_to_rds

provider_db = dbname=l_generator port=5432 host=127.0.0.1 user=postgres_migrate password=xxxxxxxxxxx
subscriber_db = dbname=l_generator port=5432 host=database.eu-west-1.rds.amazonaws.com user=l_generator password=xxxxxxxxx

# max locking time on provider (in seconds, float)
# lock_timeout = 100

# it will be used as sql ident so no dots/spaces
pgq_queue_name = londiste_queue
logfile = log/%(job_name)s.log
pidfile = run/%(job_name)s.pid

$ londiste.py londiste.ini provider install
$ londiste.py londiste.ini subscriber install

$ londiste.py londiste.ini replay -d

londiste.py londiste.ini provider add public.ad_system public.ads
londiste.py londiste.ini subscriber add public.ad_system public.ads

londiste.py londiste.ini provider tables | xargs londiste.py lonidste.ini subscriber add

lock_timeout = 100

ALTER TABLE ONLY ads_tmp ADD CONSTRAINT ads_tmp_pkey PRIMARY KEY (id);

londiste.py londiste.ini provider add-seq public.ad_system_id_seq
londiste.py londiste.ini subscriber add-seq public.ad_system_id_seq

londiste.py londiste.ini londiste.ini provider add-seq --all
londiste.py londiste.ini londiste.ini subscriber add-seq --all

 SELECT queue_name, consumer_name, lag, last_seen
   FROM pgq.get_consumer_info();

  queue_name   |              consumer_name              |       lag       |    last_seen
----------------+-----------------------------------------+-----------------+-----------------
 londiste_queue | londiste_l_genertor_migrate_to_rds | 00:00:52.128794 | 00:00:51.350834
(1 row)

$ pgqadm.py ticker.ini status
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Postgres version: 10.1   PgQ version: 2.1.8

Event queue                                    Rotation        Ticker   TLag
------------------------------------------------------------------------------
londiste_queue                                  3/7200s    500/3s/60s    27s
------------------------------------------------------------------------------

Consumer                                                       Lag  LastSeen
------------------------------------------------------------------------------
londiste_queue:
  londiste_l_genertor_migrate_to_rds                      27s       27s
  londiste_l_genertor_migrate_to_rds_copy                207s      147s
------------------------------------------------------------------------------

$ londiste.py londiste.ini subscriber tables
Table                                           State
public.ad_system                                   ok
public.ads                                    in-copy
public.adv_creator                                  -
public.adwords_disabled_ads                         -
public.alembic_version                              -

$ londiste.py londiste.ini subscriber seqs
public.ad_system_id_seq
public.ads_id_seq