PostgreSQL Quick Tips: Connecting, Dumping, Restoring

I wrote a previous post about Migrating from MySQL to PostgreSQL and some advantages, you can check it our here

Connecting, Dumping, Restoring could be done easily with a GUI tool for PostgreSQL such as pgloader.
It is open source tool and the v4.x has numerous features.




You might be need one of the following command to either connect to your postgres database locally or remotely, dump databases/schemas and also restore.
  

Connnecting to a PostgreSQL Database

Local Connection:

sudo su - postgres psql

- postgres is the pgsql user, you can replace it with other users such as root

Remote Connection:

psql -h 54.43.32.21 -p 5432 -U postgres -W password

- postgres is the pgsql user, you can replace it with other users such as root
- We included some parameters, -h represents our host= 54.43.32.21, -U represents user, -W for password.

Dumping a PostgreSQL Database

All Schemas:

pg_dump pdb > /tmp/pdb.psql

- This dumps our postgresql database including all it's schemas into a file named pdb.psql

Selecting Schemas:

pg_dump --schema=friends pdb > /tmp/pdb_friends.psql

- This dumps our friends schema alone from the pdb database into a file named pdb_friends.psql

Restoring Databases

To a local database:

psql -U postgres pdb < pdb.psql

To a remote database:

psql -h 54.43.32.21 -U postgres pdb < pgb_friends.psql


- PostgreSQL expects the pdb database to be created.
- Paramater -u stands for user, you might need to provide more parameters as we did above.
- The Schemas in the file will be imported into the database.


That's all, Drop a comment to let me know what you think. ;-)

Comments

Popular Posts