Postgresql logging parameters to log every statement

 Set the following parameters to enable Postgresql to log every statement:

 log_destination='stderr'
 logging_collection=on
 log_directory='pg_log'
 log_filename = 'postgresql-%a.log'
 log_error_verbosity=verbose
 log_min_duration_statement = 0
 log_line_prefix = '%t %r [%p]: [%l-1] user=%u,db=%d '

Here,
>  log_destination is set to 'stderr' which is default.
> logging_collection to capture plain and CSV-format log messages sent to stderr and redirects them into log files
> log_directory is set to 'pg_log' which is default
> log_filename is set to 'postgresql-%a.log' which will create log file on each day and truncate log file in every   7 days, for example, postgresql-Mon.log, postgresql-Tue.log
> log_error_verbosity is set to verbose to include the SQLSTATE error code and the source code file name, function name, and line number that generated the error
> log_min_duration_statement is set to 0. This ensures that every statement will be logged in the log file
> log_line_prefix is set to '%t %r [%p]: [%l-1] user=%u,db=%d ' where
  %t is used for time stamp,
  %r is used for remote host and port,
  %p is used for process id,
  %l is used for number of log line for each session or process starting from 1
  %u is used for user name
  %d is used for database name
 
Following is an example of the log information after setting the above parameters:

less postgresql-Mon.log
...
2011-06-06 14:56:11 BDT [local] [2051]: [3-1] user=postgres,db=postgres LOG:  00000: duration: 0.158 ms  statement: select 1;
...  

Continue reading →

ERROR: invalid byte sequence for encoding "UTF8": 0xf1612220

I was transferring data from Postgresql 8.1 to Postgresql 8.3 using pg_dump and pg_restore.

During the resotre process, I got the following error

pg_restore: [archiver (db)] Error from TOC entry 2764; 0 29708702 TABLE DATA tab_originaldata postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence for encoding "UTF8": 0xf1612220
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

I found that data have been restored succesfully for all tables except tab_originaldata for the above error.

I made a csv data file of the table from Postgresql 8.1.

copy tab_originaldata to '/tmp/tab_originaldata.csv' delimiter '|'  csv;

I imported that data in Postgresql 8.3 as follows:

show client_encoding ;
 client_encoding
-----------------
 UTF8
(1 row)
 set client_encoding to 'latin1';
copy tab_originaldata FROM '/tmp/tab_originaldata.csv' delimiter '|'  csv;

Data imported successfully.

I took backup from Postgresql 8.3 and restored in the same server to check whether the restoration was successful.

Continue reading →