How to connect to PostgreSQL with JDBC driver – Java

Author: mkyong

Jan 15

Here is a simple example to demonstrate how do connect to PostgreSQL database with JDBC driver in Java.

Go get a PostgreSQL JDBC driver first , else we can not do anything PostgreSQL JDBC Driver Download Here

Java JDBC connection always behave like following

Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection(
"jdbc:postgresql://hostname:port/dbname","username", "password");
connection.close();

Here is the full example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class JDBCExample {
  public static void main(String[] argv) {
 
	  System.out.println("-------- PostgreSQL JDBC Connection Testing ------------");
 
	  try {
	    Class.forName("org.postgresql.Driver");
 
	  } catch (ClassNotFoundException e) {
	    System.out.println("Where is your PostgreSQL JDBC Driver? Include in your library path!");
	    e.printStackTrace();
	    return;
	  }
 
	  System.out.println("PostgreSQL JDBC Driver Registered!");
 
	  Connection connection = null;
 
	  try {
 
		 connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/testdb","mkyong", "123456");
 
	  } catch (SQLException e) {
	    System.out.println("Connection Failed! Check output console");
	    e.printStackTrace();
	    return;
	  }
 
	  if (connection != null)
		  System.out.println("You made it, take control your database now!");
	  else
	    System.out.println("If you reach this line, please email me by telling how you do it?");
	  }
}
  • 1 Comment
  • Filed under: Java

    • PostgreSQL Point-in-time Recovery (Incremental Backup)

    Author: mkyong

    Nov 26

    PostgreSQL “Point-in-time Recovery” (PITR) also called as incremental database backup , online backup or may be archive backup. The PostgreSQL server records all users’ data modification transaction like insert, update or delete and write it into a file call write-ahead (WAL) log file. This mechanism use the history records stored in WAL file to do roll-forward changes made since last database full backup.

    Advantages

    1) Zero down time – The incremental database backup is important to critical system that can not afford even a minute down time. With Point-in-time Recovery, database backup down time can totally eliminated because this mechanism can make database backup and system access happened at the same time.

    2) Save storage size – with incremental database backup, we backup the latest archive log file since last backup instead of full database backup everyday.

    If above advantages are concern to you, then you should always implement incremental database backup. Here i demonstrate how to do the Point-in-time Recovery (Incremental Backup) in PostgreSQL server.

    Summary of PostgreSQL Backup Steps
    1) Modify postgresql.conf to support archive log
    2) Make a base backup (full database backup)
    3) Backup base backup to remote storage.
    4) Backup WAL (archive log files) to remote storage (continuous process)

    Summary of PostgreSQL Point-in-time Recovery Steps
    1) Extract files from base backup.
    2) Copy files from pg_xlog folder
    3) Create recovery.conf file
    4) Start Recover

    Here demostration start ~ long journey, patient…


    Database initialization


    1) Create a testing database, all database files under /usr/local/pgsql/pgDataPITR/

    Initilize database

    [mkyong]$ initdb -D /usr/local/pgsql/pgDataPITR/

    Start the database

    [mkyong]$ pg_ctl start -D /usr/local/pgsql/pgDataPITR/

    2) Make change in Postgresql configuration file (postgresql.conf), we need to make some changes in postgresql.conf file to tell PostgreSQL how to copy or archive WAL files that generated from PostgreSQL server.

    Modify postgresql.conf

    [mkyong]$ vi postgresql.conf

    Make following changes in postgresql.conf

    archive_command = on
archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f'

    Create a wals folder

    [mkyong]$ mkdir /usr/local/pgsql/pgDataPITR/wals

    Restart the database

    [mkyong]$ pg_ctl stop -D /usr/local/pgsql/pgDataPITR/
[mkyong]$ pg_ctl start -D /usr/local/pgsql/pgDataPITR/

    Attention!!! Understand how PostgreSQL handle log files, pg_xlog and archive log
    pg_xlog is a PostgreSQL log file folder that use to store all data history records. It located at /usr/local/pgsql/pgDataPITR/pg_xlog. For example, when user inserted , update or delete a record, all transaction hisroty will automatically create or append to a file log file under pg_xlog folder. Log file format is look like following format 000000010000000000000001 -> 000000010000000000000006

    For example,

    [postgres@localhost pg_xlog]$ ls -lsh
total 113M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:25 000000010000000000000006

    Every log file can handle around 16M data, when it excess this limit, it will automatically create a new log file, filename is follow 0-9 and A-Z

    000000010000000000000001
..
..
000000010000000000000009
..
..
00000001000000000000000A
..
..
00000001000000000000000Z

    This is the log files that we going to use as the roll-forward PostgreSQL Point-in-time Recovery :)

    Attention!!! Do you Still remember we configure the WAL filepath in postgresql.conf file?

    archive_command = on
archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f'

    This means when pg_xlog folder grow to certain limitation, like 6 log files each contain 16M, when PostgreSQL try to insert a new history record and detected that pg_xlog is full, it will automatically archive the oldest history log file and move it to /usr/local/pgsql/pgDataPITR/wals folder.

    Attention!!! We have to backup these archive files continuously (that why it call incremental backup :) ). We do not need to do full database backup anymore, but we do backup those archive log files constantly.

    Important log files folder

    /usr/local/pgsql/pgDataPITR/pg_xlog
/usr/local/pgsql/pgDataPITR/wals


    Data Simulation & Backup Process


    Create dummy tables and records – we will dynamic create 455,252 records in a new table, 400k records will force PostgreSQL to create enough log files in pg_xlog folder and fire the archive process to archive the log files from /usr/local/pgsql/pgDataPITR/pg_xlog to /usr/local/pgsql/pgDataPITR/wals, every logs file contain around 16M size file.

    1) Table testPITR1 created at 2008-11-25 17:17

    # psql
# select (*) from pg_class; –- contain 229 records
# select (*) from pg_description; –- contains 1988 records
 
# create table testPITR1 as select * from pg_class, pg_description; --totally 229 x 1988 = 455,252 records
# select * from current_timestamp; –-2008-11-25 17:17

    Log files look like following

    [postgres@localhost pgDataPITR]$ cd pg_xlog/
[postgres@localhost pg_xlog]$ ls -lsh
total 113M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:25 000000010000000000000006
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:16 000000010000000000000007
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000008
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000009
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 00000001000000000000000C
 
[postgres@localhost pgDataPITR]$ cd wals
[postgres@localhost wals]$ ls -lsh
total 97M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000000
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000001
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000002
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000003
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000004
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000005

    2) Create a full databse backup – base backup

    # psql
# select pg_start_backup(’Full Backup - Testing’);
 
pg_start_backup()
——————–
0/6BA9328
(1 row)

    pg_start_backup is use to create a label, and log it into log file. (actually this is optional, good habit)

    Use a tar command to compress all pgDataPITR folder to make a database base backup.

    tar -cvzf /usr/local/pgsql/pgDataPITR/pgdatabk20081125.tar /usr/local/pgsql/pgDataPITR/

    Remember !!! pgdatabk20081125.tar this is the full database backup (base backup) including Postgresql configuration , system and all others files and folder.

    pg_stop_backup() create a label in log file as well. (actually this is optional, good habit)

    select pg_stop_backup();
————————
0/6BA9384
(1 row)

    3) Table testPITR2 created at 2008-11-25 18:08:06 –prepare for Point-in-time Recovery

    # psql
# create table testPITR2 as select * from pg_class, pg_description; 
# select * from current_timestamp; --2008-11-25 18:08:06
    [postgres@localhost pgDataPITR]$ cd pg_xlog/
[postgres@localhost pg_xlog]$ ls -lsh
total 113M
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000C
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:08 00000001000000000000000D
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:06 00000001000000000000000E
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000F
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000010
 
[postgres@localhost wals]$ ls -lsh
total 209M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000000
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000001
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000002
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000003
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000004
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000005
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:02 000000010000000000000006
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:06 000000010000000000000007
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000008
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000009
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000C

    P.S The pg_start_backup() and pg_stop_backup() backup labels will created in 000000010000000000000006.00BA9328.backup file. This is a good habit to make a label here.

    [postgres@localhost wals]$ cat 000000010000000000000006.00BA9328.backup
START WAL LOCATION: 0/6BA9328 (file 000000010000000000000006)
STOP WAL LOCATION: 0/6BA9384 (file 000000010000000000000006)
CHECKPOINT LOCATION: 0/6BA9328
START TIME: 2008-11-25 17:45:24 MYT
LABEL: Full Backup - Testing
STOP TIME: 2008-11-25 18:02:18 MYT

    3) Table testPITR3 created at 2008-11-25 18:15:23 –prepare for Point-in-time Recovery

    # psql
# create table testPITR3 as select * from pg_class, pg_description; 
# select * from current_timestamp; --–2008-11-25 18:15:23
    [postgres@localhost pg_xlog]$ ls -lsh
total 129M
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000010
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000011
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000012
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000013
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000014
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000015
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000016
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000017
 
[postgres@localhost wals]$ ls -lsh
total 321M
 
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000000
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000001
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000002
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000003
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000004
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000005
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:02 000000010000000000000006
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:06 000000010000000000000007
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000008
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000009
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000C
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000D
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000E
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000F
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000010
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000011
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000012
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000013

    4) Table testPITR4 created at 2008-11-25 20:00:04 –prepare for Point-in-time Recovery

    # psql
# create table testPITR4 as select * from pg_class, pg_description; 
# select * from current_timestamp; –-2008-11-25 20:00:04
    [root@localhost pg_xlog]# ls -lsh
total 129M
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000017
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000018
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000019
17M -rw——- 1 postgres postgres 16M 2008-11-25 20:01 00000001000000000000001A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 00000001000000000000001B
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:58 00000001000000000000001C
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:58 00000001000000000000001D
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 00000001000000000000001E
 
[root@localhost wals]# ls -lsh
total 417M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000000
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000001
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000002
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000003
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000004
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000005
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:02 000000010000000000000006
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:06 000000010000000000000007
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000008
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000009
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000C
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000D
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000E
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000F
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000010
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000011
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000012
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000013
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:58 000000010000000000000014
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:58 000000010000000000000015
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000016
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000017
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000018
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000019

    We created 4 tables for PITR recovery, remember table creation time, we going to do recovery base on the time later.

    Table testPITR1 created at 2008-11-25 17:17
Table testPITR2 created at 2008-11-25 18:08:06
Table testPITR3 created at 2008-11-25 18:15:23
Table testPITR4 created at 2008-11-25 20:00:04
    postgres=# \d
List of relations
Schema | Name | Type | Owner
——–+———–+——-+———-
public | testpitr1 | table | postgres
public | testpitr2 | table | postgres
public | testpitr3 | table | postgres
public | testpitr4 | table | postgres
(4 rows)

    Attention!!! Before move on, please study above transaction log files movement that generated by PostgreSQL. We have to fully understand when PostgreSQL will create a new log file and when it will move to archive folder, do not forget the log file format :) ~ take sometime to review and understand the above log file generation sequence


    Disaster come in


    We have to do something in order to make our PostgreSQL server go down.

    1) Kill the postgresql process

    kill -9 $(head -1 /usr/local/pgsql/pgDataPITR/postmaster.pid)

    2) Just turn off your Power :)


    Recovery Process


    Finally we reach recovery process, Please remember 1 file and 2 folders

    a) Base backup file – pgdatabk20081125.tar
    b) Log files hanv’t archive yet – all files under Pg_xlog folder
    c) WALs – all archive files under wals folder (may be a remote storage in real environment)

    Remember!!! Assume we already backup pgdatabk20081125.tar and all archived files to pgbackup folder under /usr/local/pgsql/pgbackup

    $ mkdir /usr/local/pgsql/pgbackup/pgbackup
$ mv pgdatabk20081125.tar /usr/local/pgsql/pgbackup/pgbackup
$ cp -r /usr/local/pgsql/pgDataPITR/wals /usr/local/pgsql/pgbackup/pgbackup

    1) Rename pgDataPITR to pgDataPITR.bad.data, assume database file in pgDataPITR folder was damaged due to disaster we created just now, we need to create a fresh database later.

    $ mv pgDataPITR pgDataPITR.bad.data

    2) Unzip / extract files from pgdatabk20081125.tar, create a new pgDataPITR folder under /usr/local/pgdata/, it just like what we did before. Move all extracted files from pgdatabk20081125.tar to /usr/local/pgsql/pgDataPITR.

    $ tar -xvzf pgdatabk20081125.tar 
[extract-path/pgDataPITR]$ mv * /usr/local/pgsql/pgDataPITR

    Start database

    $ ./pg_ctl start -D /usr/local/pgsql/pgDataPITR/
$ psql
 
postgres=# \d
List of relations
Schema | Name | Type | Owner
——–+———–+——-+———-
public | testpitr1 | table | postgres

    Table testPITR1 created at 2008-11-25 17:17 is restored. This testPITR1 table is created before base backup process launched, so this is correct.

    3) Copy log files from pg_xlog folder. Some log files still located in pgDataPITR.bad.data pg_xlog folder (those log files hanv’t archive yet) during disaster happening, we need to copy the log file back and recover it as much as possible.

     cp /usr/local/pgsql/pgDataPITR.bad.data/pg_xlog/0* /usr/local/pgsql/pgDataPITR

    4) Create a recovery.conf file and put it under /usr/local/pgsql/pgDataPITR

    vi recovery.conf

    Create following content in recovery.conf 

    restore_command = 'cp /usr/local/pgsql/pgbackup/wals/%f %p'
recovery_target_time = '2008-11-25 18:08:06'

    Attention !!! This is the final process and most critical backup process

    a) /usr/local/pgsql/pgbackup/wals/ is the folder that we backup our archive log files
    b) recovery_target_time is the time we need to recover to. Omit this setting will make PostgreSQL recover as much as it can, it may recover all changes.

    Remember four tables creation time
Table testPITR1 created at 2008-11-25 17:17:00
Table testPITR2 created at 2008-11-25 18:08:06
Table testPITR3 created at 2008-11-25 18:15:23
Table testPITR4 created at 2008-11-25 20:00:04

    Remember !!! Above recovery.conf file will make PostgreSQL take the archive log files from /usr/local/pgsql/pgbackup/wals/ folder and recover the data changes until 2008-11-25 18:08:06 (table testPITR2 created).

    5) Start database and output log file to /usr/local/pgsql/pgDataPITR/pg.log

    $ pg_ctl start -D /usr/local/pgsql/pgDataPITR -l /usr/local/pgsql/pgDataPITR/pg.log
$ psql
postgres=# \d
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | testpitr1 | table | postgres
 public | testpitr2 | table | postgres
(2 rows)

    Table testpitr2 is restored back.

    P.S After recovery process finished, recovery.conf will rename to recovery.done by PostgreSQL to avoid start the recovery process again.

    We can the view pg.log file to understand how PostgreSQL process the recovery process.

    [postgres@localhost pgDataPITR]$ cat pg.log
STATEMENT:  select pg_start_backup("Full Backup - Testing");
LOG:  database system was interrupted; last known up at 2008-11-25 17:45:23 MYT
LOG:  starting archive recovery
LOG:  restore_command = 'cp /usr/local/pgsql/pgbackup/wals/%f %p'
LOG:  recovery_target_time = '2008-11-25 18:08:06+08'
cp: cannot stat `/usr/local/pgsql/pgbackup/wals/00000001.history': No such file or directory
LOG:  restored log file "000000010000000000000006.00BA9328.backup" from archive
LOG:  restored log file "000000010000000000000006" from archive
LOG:  automatic recovery in progress
LOG:  redo starts at 0/6BA9368
LOG:  restored log file "000000010000000000000007" from archive
LOG:  restored log file "000000010000000000000008" from archive
LOG:  restored log file "000000010000000000000009" from archive
LOG:  restored log file "00000001000000000000000A" from archive
LOG:  restored log file "00000001000000000000000B" from archive
LOG:  restored log file "00000001000000000000000C" from archive
LOG:  restored log file "00000001000000000000000D" from archive
LOG:  recovery stopping before commit of transaction 395, time 2008-11-25 18:08:34.180397+08
LOG:  redo done at 0/D85E0FC
LOG:  last completed transaction was at log time 2008-11-25 18:08:34.180397+08

    Attention !!!Attention !!! THIS IS ONE TIME PROCESS, after recovery process started and finished, we cant make any recovery changes (like roll forward to another time).

    If we want to roll forward to another restore time, we need to start whole recovery process again, like extract files from base backup and copy log files. This is because after PostgreSQL recovered the data , all log files format will changed to other format like following

    [postgres@localhost pgDataPITR]$ cd pg_xlog/
[postgres@localhost pg_xlog]$ ls -ls
total 147696
16408 -rw------- 1 postgres postgres 16777216 2008-11-26 14:28 00000002000000000000000D
    8 -rw------- 1 postgres postgres       83 2008-11-26 14:22 00000002.history
16408 -rw------- 1 postgres postgres 16777216 2008-11-26 14:30 00000003000000000000000D
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:16 00000003000000000000000E
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 00000003000000000000000F
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 000000030000000000000010
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:45 000000030000000000000011
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 000000030000000000000012
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 000000030000000000000013
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 000000030000000000000014
    8 -rw------- 1 postgres postgres      158 2008-11-26 14:30 00000003.history

    After recovered, log file number will increased
    00000001 –> 00000002 –> 00000003

    If we want to restore table testPITR3 created at 2008-11-25 18:15:23, we are unable to do it, it will output error in log file unless we start the whole recovery process again.


    Conclusion


    This archive log files transaction backup and restore mechanism is implemented in many enterprise database like Oracle. The archive log files backup concept is very important, please do some real practice and master it, it really help when your database went down.

    Thanks for reading this long article, please correct me if i write something wrong :) hope help.

    PostgreSQL Official Point-in-time Recovery Reference
    http://www.postgresql.org/docs/8.0/interactive/backup-online.html

  • Comments Off
  • Filed under: Database

    • How to export table data to file / csv – PostgreSQL

    Author: mkyong

    Sep 9

    My boss ask me export some data from database for him to do statistic report. PostgreSQL provided easy and useful export feature to do it. Here i will demonstrate how to export data or query result from PostgreSQL into a file or csv file.

    1) Connect to PostgreSQL with psql command

    yongmo@abcdb:~$ psql -p 5433 -U dba dbname

    P.S i installed my PostgreSQL in port 5433, so i need to specific port number.
    P.S Type \? to view all available command

    2) Type \o /home/yongmo/data25000.csv, it will export query result to /home/yongmo/data25000.csv file.

    dbname=> \o /home/yongmo/data25000.csv

    3) Type query that you want to export

    dbname=> select url from urltable where scoreid=1 limit 25000;

    4) Done, all query result exported to /home/yongmo/data25000.csv

    Here is the full command

    yongmo@abcdb:~$ psql -p 5433 -U dba dbname
Password for user dba: 
Welcome to psql 8.2.4 (server 8.3.3), the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
 
WARNING:  You are connected to a server with major version 8.3,
but your psql client is major version 8.2.  Some backslash commands,
such as \d, might not work properly.
 
dbname=> \o /home/yongmo/data25000.csv
dbname=> select url from urltable where scoreid=1 limit 25000;
dbname=> \q
  • 16 Comments
  • Filed under: Database

    • How to kill / terminate PostgreSQL hang query

    Author: mkyong

    Sep 3

    Today i find out one of the query is hanging in PostgreSQL, it can’t release itself, it just seem like stuck in PostgreSQL forever.

    I issue a command to cancel it in PostgreSQL, but it seem not working. Query just hanging there and show an “idle in transaction” status. I have no way to terminal it, it left me no choice but go Debian terminal to issue kill command to terminate it manually.

    Query hanging or not responding in PostgreSQL is because i didn’t handle transaction manager properly in my web application. When System accidently shut down, running query will hanging in PostgreSQL and my transaction manager (DataSourceTransactionManager) is not manage to rollback whole transaction. I have no idea why it’s not working. After i changed my transaction manager to JtaTransactionManager, it work correctly and no transaction hanging in middle way when system accidentally shut down.

    However here i show how to terminal the hanging query in PostgreSQL. The hanging query will display as “idle in transaction” in PostgreSQL. I have to list out all PostgreSQL processes and issue a kill terminate command to terminate the query manually.

    1) Issue ps -ef | grep postgres to list out all processes belong to postgres user.

    mkyong:~# ps -ef | grep postgres
postgres 13648     1  0 11:04 ?        00:00:00 /var/lib/postgresql/PostgresPlus8.3xxxxxxx
postgres 13651 13648  0 11:04 ?        00:00:00 postgres: logger process                                                                        
postgres 13653 13648  0 11:04 ?        00:00:00 postgres: writer process                                                                        
postgres 13654 13648  0 11:04 ?        00:00:00 postgres: wal writer process                                                                    
postgres 13655 13648  0 11:04 ?        00:00:00 postgres: autovacuum launcher process                                                           
postgres 13656 13648  0 11:04 ?        00:00:00 postgres: stats collector process                                                               
postgres 13668 13648  0 11:04 ?        00:00:00 postgres: postgres postgres 10.70.1.27(3734) idle                                               
postgres 13689 13648  0 11:05 ?        00:00:00 postgres: usrdba db_test 10.70.1.67(4164) idle                                               
postgres 13714 13648  0 11:06 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57586) idle in transaction                               
postgres 13721 13648  0 11:06 ?        00:00:16 postgres: usrdba db_test 10.70.1.67(4165) idle                                               
postgres 13832 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57592) idle                                              
postgres 13833 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57593) idle                                              
postgres 13834 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57594) idle                                              
postgres 13835 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57595) idle                                              
postgres 13836 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57596) idle                                              
postgres 14201 13648  0 11:23 ?        00:00:00 postgres: nrsdba postgres 10.70.1.8(4419) idle                                                  
postgres 14202 13648  0 11:23 ?        00:00:00 postgres: usrdba db_test 10.70.1.8(4420) idle                                                
postgres 14207 13648  0 11:24 ?        00:00:00 postgres: usrdba db_test 10.70.1.8(4421) idle                                                
root     18030 17992  0 13:46 pts/0    00:00:00 grep postgres
mkyong:~#

    2) Notice process id 13714, idle in transaction, this is the hanging query in PostgreSQL. Issue following command to terminate the PostgreSQL process manually.

    mkyong:~# kill 13714

    or

    mkyong:~# kill -TERM 13714

    or

    mkyong:~# kill -15 13714

    3) Done, hanging query is gone

    mkyong:~# ps -ef | grep postgres
postgres 13648     1  0 11:04 ?        00:00:00 /var/lib/postgresql/PostgresPlus8.3xxxxxxx
postgres 13651 13648  0 11:04 ?        00:00:00 postgres: logger process                                                                        
postgres 13653 13648  0 11:04 ?        00:00:00 postgres: writer process                                                                        
postgres 13654 13648  0 11:04 ?        00:00:00 postgres: wal writer process                                                                    
postgres 13655 13648  0 11:04 ?        00:00:00 postgres: autovacuum launcher process                                                           
postgres 13656 13648  0 11:04 ?        00:00:00 postgres: stats collector process                                                               
postgres 13668 13648  0 11:04 ?        00:00:00 postgres: postgres postgres 10.70.1.27(3734) idle                                               
postgres 13689 13648  0 11:05 ?        00:00:00 postgres: usrdba db_test 10.70.1.67(4164) idle                                                                         
postgres 13721 13648  0 11:06 ?        00:00:16 postgres: usrdba db_test 10.70.1.67(4165) idle                                               
postgres 13832 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57592) idle                                              
postgres 13833 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57593) idle                                              
postgres 13834 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57594) idle                                              
postgres 13835 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57595) idle                                              
postgres 13836 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57596) idle                                              
postgres 14201 13648  0 11:23 ?        00:00:00 postgres: nrsdba postgres 10.70.1.8(4419) idle                                                  
postgres 14202 13648  0 11:23 ?        00:00:00 postgres: usrdba db_test 10.70.1.8(4420) idle                                                
postgres 14207 13648  0 11:24 ?        00:00:00 postgres: usrdba db_test 10.70.1.8(4421) idle                                                
root     18030 17992  0 13:46 pts/0    00:00:00 grep postgres
mkyong:~#
  • 0 Comments
  • Filed under: Database

    • TO_DATE function between PostgreSQL 8.2 and 8.3

    Author: mkyong

    Aug 22

    As we all know PostgreSQL 8.3 make very strong checking on data type, it make a lot application hit many data type error after migration from PostgreSQL8.x to PostgreSQL8.3. Mostly is cause by data type checking.

    One of the common error is to_date() function. It accept two text as parameters.

    to_date(text,text)

    Ok now i create a table as following. A simple table call n_url_test contain a createddate as timestamp data type.

    CREATE TABLE n_url_test
(
  urltestid bigserial NOT NULL,
  createddate timestamp without time zone DEFAULT now(),
  CONSTRAINT n_url_to_test_pkey PRIMARY KEY (urltestid)
)

    I run following sql in PostgreSQL8.2, it return my expected result as ‘YYYY-MM-DD’ format.

    --PostgreSQL 8.2
---------------
SELECT  to_date(createddate,'YYYY-MM-DD') FROM n_url_test;

    However after i migrated to PostgreSQL8.3, i hit following error

    --PostgreSQL 8.3
---------------
SELECT  to_date(createddate,'YYYY-MM-DD') FROM n_url_test;
 
--------------------------------------------------------------------------------
ERROR:  FUNCTION to_date(timestamp without time zone, unknown) does NOT exist
LINE 1: SELECT  to_date(createddate,'YYYY-MM-DD') FROM n_url_test
                ^
HINT:  No FUNCTION matches the given name AND argument types. 
You might need TO ADD explicit type casts.
 
********** Error **********

    PostgreSQL 8.3 will not automatically convert from timestamp to text for us like what it does in previous version. We need to explicit type casts createddate like following

    --PostgreSQL 8.3
---------------
SELECT  to_date(createddate::text,'YYYY-MM-DD') FROM n_url_test;

    Append ::text to createddate will explicit convert datatype as text, It’s work. PostgreSQL8.3 strictly checking on data type is good function , but please do not forget there still have a lot people using previous version. It cause a lot problem if application is design base on ignore data type checking like what i mention above.

  • 0 Comments
  • Filed under: Database
    • View more posts »