Issue encounter and simple note on PostgreSQL exploration

How to export and import sqldump file into PostgreSQL database?

Note: 2 format types of sqldump that PostgreSql can produce :-
- copy format
- sql format

In case to avoid problems that I encountered by using "copy" format, I advice to use "sql" format.

For my scenario, I use phpPgAdmin to export sqldump. Below are the steps to export sqldump from the source of the database server and import to the target of the database server:
1. Login through phpPgAdmin to access the source database server.
2. Tap on database name in the left menu
3. Tap on "Export" tab in the right hand screen
4. Select "Structure & Data" option with "SQL" format as displayed in the screen below

5. Click on "Export" button, sqldump file in sql extension will be downloaded.
6. Use pgAdminIII tool, create the database in your new target database server.
7. Open the query editor, open the sqldump script and execute the script.
8. Tables structure will be created and all the data will be imported to the target database server.

How to export database schema with command line?

Assume the parameters below represent
Database User ID: [USER_ID]
Database Name: [DATABASE_NAME]
SQL output file: [SQL_FILE]

pg_dump --schema-only -U [USER_ID] [DATABASE_NAME] > [SQL_FILE]

Note: Applicable on Linux

How to export database dump and import into newly created database?

Assume the parameters below represent
Database user id, for example, 'postgres' : [DB_USER_LOGIN]
Database name, for example, 'mydatabase' : [DATABASE_NAME]
Database dump file, for example, 'dump.sql' : [DATABASE_DUMP_FILE]

1. Export database dump from source database using the command as described below (Without copy owner and privileges)

pg_dump --no-owner --no-privileges -U [DB_USER_LOGIN] [DATABASE_NAME] > [DATABASE_DUMP_FILE]

2. Create [DATABASE_NAME] database in your POSTGRES database server

3. Import database dump to the target database using the command as described below

psql -U [DB_USER_LOGIN] [DATABASE_NAME] < [DATABASE_DUMP_FILE]

How to create users and new PostgreSQL database through command line?

Assume the parameters below represent
New user id, for example, 'george' : [NEW_DB_USER_LOGIN]
New user password, for example, '12345678' : [NEW_DB_USER_PASSWORD]
New Database name, for example, 'mydatabase' : [NEW_DATABASE_NAME]

1. In the Linux terminal, type in the command below to add new user

adduser [NEW_DB_USER_LOGIN]

Note: If command not found error message is prompted when 'adduser' is used, type in '/usr/sbin/adduser' to create user.

 2. Use the command below to reset the new user's password

passwd [NEW_DB_USER_LOGIN]

3. Create a new database based on the database template1

psql -d template1 -U postgres

4. Create new user

template1=#CREATE USER [NEW_DB_USER_LOGIN] WITH PASSWORD '[NEW_DB_USER_PASSWORD]';

5. Create new database

template1=#CREATE DATABASE [NEW_DATABASE_NAME];

6. Grant new user access right to the new database

template1=#GRANT ALL PRIVILEGES ON DATABASE [NEW_DATABASE_NAME] to [NEW_DB_USER_LOGIN];

7. Type in '\q' to quit the template1.