Copy Databse of Postgresql | Import & Export Remote to Local

 1. First Login Using

     sudo -i -u postgres

 Then Type:

    psql 

To see databse list:

    \l or \list

Connect to Database

    \c ebook_mcq 

List Table

    \dt  

Now Get Out of The Database By

exit

To Save A Copy as File

pg_dump mydb > mydb.sql

Now in your local Computer from another terminal download the file

scp -r username@ip:/var/lib/postgresql/mydb.sql .

Copy Local File to Remote

scp -r /local/directory remote_username@10.10.0.2:/remote/directory

To Import  

psql -U username dbname < dbexport.pgsql

if you want to delete all the tables

DO $$ 

  DECLARE 

    r RECORD;

BEGIN

  FOR r IN 

    (

      SELECT table_name 

      FROM information_schema.tables 

      WHERE table_schema=current_schema()

    ) 

  LOOP

     EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.table_name) || ' CASCADE';

  END LOOP;

END $$ ;

Or,

DROP SCHEMA public CASCADE; CREATE SCHEMA public;