PostgreSQL Data Migration

PostgreSQL prepares new library from library failure

1, create user
[Email protected] home]# Userdel Postgres
[Email protected] home]# Groupdel Postgres

Mkdir/home/mydb
Chown-r Postgres:postgres/home/mydb
2. Unpack the installation package
[Email protected] home]## TAR-XVF Postgres/postgresql-8.1.4.tar

CD postgres/postgresql-8.1.4
Compile and install:./configure--prefix=/usr/local/pgsql-localstatedir=/home/mydb

$./configure--prefix=/usr/local/pgsql--with-pgconfigdir=/usr/local/pgsql/bin/--with-pgbindir=/usr/local/pgsql/ bin/--with-pgincludedir=/usr/local/pgsql
/include/--with-pgincludeserverdir=/usr/local/pgsql/include/server/--with-pglibdir=/usr/local/pgsql/lib/-- with-pgpkglibdir=/usr/local/pgsql/lib/--with-pgsharedir=/usr/local/pgsql/share/
Make

All of PostgreSQL successfully made. Ready to install

Su-postgres

VI. bash_profile

Path= $PATH: $HOME/bin:/usr/local/pgsql/bin

/usr/local/pgsql/bin/initdb/home/mydb

/usr/local/pgsql/bin/pg_ctl-d/home/mydb Star

3.slony Installation

TAR-XVF Slony1-1.2.6.tar

Su-root

./configure--with-pgsourcetree=/usr/local/pgsql/bin

$./configure--prefix=/usr/local/pgsql--with-pgconfigdir=/usr/local/pgsql/bin/--with-pgbindir=/usr/local/pgsql/ bin/--with-pgincludedir=/usr/local/pgsql
/include/--with-pgincludeserverdir=/usr/local/pgsql/include/server/--with-pglibdir=/usr/local/pgsql/lib/-- with-pgpkglibdir=/usr/local/pgsql/lib/--with-pgsharedir=/usr/local/pgsql/share/

3. Backup of the main library data

/usr/local/pgsql/bin/pg_dumpall >/home/mydb/backup/' Date ' +%y%m%d ' _dumpall.sql

4. New Library Recovery

Psql-f/home/mydb/backup/' date ' +%y%m%d ' _dumpall.sql

Error found:

Psql:20150611_dumpall.sql:406:error:could not Access file "$libdir/dblink": No such file or directory
Psql:20150611_dumpall.sql:409:error:function Public.dblink (text, text) does not exist
Psql:20150611_dumpall.sql:417:error:could not Access file "$libdir/dblink": No such file or directory
Psql:20150611_dumpall.sql:420:error:function Public.dblink (text, Text, Boolean) does not exist
Psql:20150611_dumpall.sql:428:error:could not Access file "$libdir/dblink": No such file or directory
Psql:20150611_dumpall.sql:431:error:function Public.dblink (text) does not exist
Psql:20150611_dumpall.sql:439:error:could not Access file "$libdir/dblink": No such file or directory
Psql:20150611_dumpall.sql:442:error:function Public.dblink (Text, Boolean) does not exist
Psql:20150611_dumpall.sql:450:error:could not Access file "$libdir/dblink": No such file or directory
Psql:20150611_dumpall.sql:453:error:function public.dblink_build_sql_delete (text, Int2vector, Integer, text[]) does Not exist
Psql:20150611_dumpall.sql:461:error:could not Access file "$libdir/dblink": No such file or directory
Psql:20150611_dumpall.sql:464:error:function Public.dblink_build_sql_insert (text, Int2vector, Integer, text[], text []) does not exist
Psql:20150611_dumpall.sql:472:error:could not Access file "$libdir/dblink": No such file or directory
Psql:20150611_dumpall.sql:475:error:function public.dblink_build_sql_update (text, Int2vector, Integer, text[], text []) does not exist
Psql:20150611_dumpall.sql:483:error:could not Access file "$libdir/dblink": No such file or directory
Psql:20150611_dumpall.sql:486:error:function public.dblink_close (text) does not exist

The reason for the error is that stored procedures are used in the database and stored procedures are used to Dblink

Configuring the Installation Dblink (extended)

Cd/home/postgres/postgresql-8.1.4/contrib
Make

Make install

It's no problem to recover.

5. The old scripts from the library and the Slon script files are moved to the new library, and the IP is modified from the library IP

PostgreSQL Data Migration