Step by step Table migration from AWS RDS postgreSQL to other PostgreSQL DB instance

 Step by step Table migration from AWS RDS postgreSQL to other PostgreSQL DB instance:

steps:

1.use the pg_dump to take the backup to plain file

2.restore on target using psql import.

pg_dump --username=db_admin --host=<host_name_aws endpoint> --port=5700 --format=plain --file=backup.sql  --dbname=postgrsql_db_dev  --table=test_table1

-rw-r--r-- 1 postgres postgres  2294 Nov 26 08:30 backup.sql

<server_name>:~/aws> cat backup.sql

--

-- PostgreSQL database dump

--

-- Dumped from database version 11.5

-- Dumped by pg_dump version 12.1

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET xmloption = content;

SET client_min_messages = warning;

SET row_security = off;

SET default_tablespace = '';

--

-- Name: test_table1; Type: TABLE; Schema: public; Owner: app456_admin

--

CREATE TABLE public.test_table1 (

    id integer NOT NULL,

    app_id integer NOT NULL,

    variant_id integer NOT NULL,

    principal_id integer NOT NULL,

    include boolean NOT NULL

);

ALTER TABLE public.test_table1 OWNER TO app456_admin;

--

-- Name: test_table1_id_seq; Type: SEQUENCE; Schema: public; Owner: app456_admin

--

CREATE SEQUENCE public.test_table1_id_seq

    AS integer

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

ALTER TABLE public.test_table1_id_seq OWNER TO app456_admin;

--

-- Name: test_table1_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: app456_admin

--

ALTER SEQUENCE public.test_table1_id_seq OWNED BY public.test_table1.id;

--

-- Name: test_table1 id; Type: DEFAULT; Schema: public; Owner: app456_admin

--

ALTER TABLE ONLY public.test_table1 ALTER COLUMN id SET DEFAULT nextval('public.test_table1_id_seq'::regclass);

--

-- Data for Name: test_table1; Type: TABLE DATA; Schema: public; Owner: app456_admin

--

COPY public.test_table1 (id, app_id, variant_id, principal_id, include) FROM stdin;

4       80      43      51      t

5       89      52      51      t

\.

--

-- Name: test_table1_id_seq; Type: SEQUENCE SET; Schema: public; Owner: app456_admin

--

SELECT pg_catalog.setval('public.test_table1_id_seq', 5, true);

--

-- Name: test_table1 test_table1_pkey; Type: CONSTRAINT; Schema: public; Owner: app456_admin

--

ALTER TABLE ONLY public.test_table1

    ADD CONSTRAINT test_table1_pkey PRIMARY KEY (id);

--

-- Name: test_table1_by_app_id_variant_id; Type: INDEX; Schema: public; Owner: app456_admin

--

CREATE INDEX test_table1_by_app_id_variant_id ON public.test_table1 USING btree (app_id, variant_id);

--

-- PostgreSQL database dump complete

--

***************************restore using import**************************************

psql --username=testuser --host=<host_name> --port=5432  --file=backup.sql  --dbname=testdb  --table=test_table1

<server_name>:~/aws> psql --username=testuser --host=<host_name> --port=5432  --file=backup.sql  --dbname=testdb  --table=test_table1

SET

SET

SET

SET

SET

 set_config

------------

(1 row)

SET

SET

SET

SET

SET

CREATE TABLE

ALTER TABLE

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

ALTER TABLE

COPY 2

 setval

--------

      5

(1 row)

ALTER TABLE

CREATE INDEX

<server_name>:~/aws> psql

psql (12.1, server 11.6)

Type "help" for help.

postgres=# \c testdb

psql (12.1, server 11.6)

You are now connected to database "testdb" as user "postgres".

after Data migration to  new PostgreSQL instance

testdb=# select * from test_table1;

 id1 | app_id | variant_id | principal_id | include

----+--------+------------+--------------+---------

  14 |     480 |         543 |          751 | t

  45 |     489 |         552 |          751 | t

(2 rows)