How to dump all view in PostgreSQL Database Server

Posted on

It is quite tricky actually for having the task specified in the title of this article to be done. The task is actually need some requirement in order for dumping all view available in PostgreSQL Database Server executed successfully in the end. This article written in order to show how to dump all view in PostgreSQL Database Server with the following conditions :

1. The existance of PostgreSQL Database Server which is going to be accessed. It is true, because in order to dump the view available in a certain database, first of all, it must be connected and it must be accessed.

2. The connection to PostgreSQL Command Console need to be done. As soon as the connection has successfully carried out just type the associated command responsible for dumping certain view exist in PostgreSQL Database Server. Basically, it can be done using the usual command, but to be able to dump all view in detail, there are several things need to be done before. It is actually the naming of the view. In order to differentiate view and table, just name the view with a different way with the table. By using specific naming of view, to be able to remove all the views based on the pattern of the specific naming, it can be done smoothly. For an example, a view can be named using a specific pattern as : “v_view_name”. Based on the pattern, it can be easily dumped using that specific pattern. Below is how to dump all views using that specific pattern :

pg_dump -Uusername -s -t “pattern_specific” > /dump_file_name_and_location.dump

For an example :

user@hostname:~$ pg_dump -Upostgres -s -t "v_*" database_name > /home/user/view.dump

The file has already been dumped successfully with the dump file named ‘view.dump’ located in ‘/home/user’. It is executed using postgres as the username with the additional parameter -t for dumping table or view and an additional parameter -s for dumping schema only without the data contained in the table. The table or the view has the pattern name of “v_*”. Since, there is an agreement done before creating the database where every view must be named using specific pattern of “v_*”, there will be only schemas dumped to the dump file. The view which is dumped exist in a database named ‘database_name’.

Leave a Reply