Phelio Gnomi

My words from my mind

PostgreSQL Input and Output

NOTE: this is one way of doing it. Find the better solution here.

Let’s imagine a scenario where you have 10 different SQL queries to run and they will take few hours each. You don’t want to run it in the peak hours because it will slow down your entire server. You don’t want to stay back late either. So you decided to put all the queries in a text file and run them with your PostgreSQL client at the end of the day and you go for a bottle of beer at the nearest club and call it a day.

Here is how you do it in PostgreSQL. First, fire up your PostgreSQL client such as

psql -h your-host-name -U your-username -d your-dbname

and then, you run the \i command. Well, “i” is obviously stands for “input”.

\i /path/to/your/sql/file

VOILA, the sql are running. And then you pack your stuffs and call up your friends to meet you up at that bar you talked about earlier. But wait a minute. What if something went wrong? what if my console suddenly close itself? I won’t know if all the queries ran successfully or not. Don’t worry, you say. We can stream the output into a log file. And when we come back tomorrow morning, we will know where to check. And so, you pressed CTRL+C to cancel your previous job and waited a while until it finish cancelling it and then you start stomping some other commands:

\o /path/to/your/log/file
\i /path/to/your/sql/file

And voila!! you can really go to that pub where your friends are waiting now. Have a good evening, folks!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: