PgEasyPart

From SemanticLab

Jump to: navigation, search

Contents

Introduction

Partitioning in PostgreSQL databases is currently a very hard task, as the user has to add partition constraints, manually write triggers and trigger functions and manually check for overlapping partitions. Furthermore every time a new partition is added, new triggers have to be written and the existing trigger functions have to be adjusted. Partitioning in PostgreSQL gets even harder if one wants to partition an existing non-partitioned table that already contains data.

PgEasyPart simplifies all this tasks. It helps the user to create and manage partitions in PostgreSQL databases. The user can perform all these tasks with a single SQL-like command.

Features of pgEasyPart

  • Create a new partitioned table
  • Add new partitions to a partitioned table
  • Partition an existing non-partitioned table
  • Split a partition
  • Merge two partitions
  • Detach a partition from a partitioned table
  • Attach a partition to a partitioned table
  • Check PRIMARY and FOREIGN KEY's
  • Supports LIST- and RANGE-partitioning

Currently not supported features

  • sub-partitions
  • multi-column partition-keys

Versions

Two versions of pgEasyPart exist: The original one by Daniel Sturm (SVN) and a slightly modified version by Heinz-Peter Lang (SVN). The installation and usage of both versions will be explained in the later of this page.

Installation and usage

System requirements: Linux; PostgreSQL 8.3 or 8.4; Java JRE 6

Don't forget to take a look at the README file before using pgEasyPart. Additionally you may want to edit the pgEasyPart.conf File (you should at least have a look at this file, it also contains an explanation of each parameter).

PgEasyPart depends on the PostgreSQL JDBC Driver. The PostgreSQL driver (or a symbolic link to the driver) must be placed to: 

   /usr/share/java/postgresql.jar

Original version

Just change into the pgEasyPart folder and start pgEasyPart with the command: 

    java -jar pgEasyPart.jar

or use the pgEPAddPartition or the pgEPCheck command as shown in the USAGE document.


Updated version

  • Copy the included configuration file pgEasyPart.properties to the folder .pgEasyPart in your home-directory 
mkdir $HOME/.pgEasyPart
cp pgEasyPart.properties $HOME/.pgEasyPart
  • Change to the path where PgEasyPartCLI.jar is stored.
  • Run the tool with 
java -jar PgEasyPartCLI.jar
  • Commands: 
usage: pgEasyPart
 -db,--dbName <arg>      database name
 -f,--configFile <arg>   use this file instead of the default config
                        ($HOME/.pgEasyPart/pgEasyPart.conf)
 -h,--host <arg>         db-host (leave empty for localhost)
 -p,--port <arg>         database port
 -stmtFile <arg>         File with a statements to run
 -U,--user <arg>         database user
 -W,--password <arg>     database password

The only mandatory parameter is stmtFile, which contains the pgEasyPart-Statement to execute. The statements are mentioned in detail below. Please make sure not to add a semicolon at the end of the line.

If the other parameters aren't set, the tools tries to read default config-file located at '$HOME/.pgEasyPart/pgEasyPart.properties'. All other parameters should be self-explanatory.

Some Examples

Create a new partitioned table: 

   CREATE TABLE s.foo (
       a integer PRIMARY KEY,
       b integer,
       c char(2),
       d date,
       FOREIGN KEY (b, c) REFERENCES s.bar_1 (a, b)
   ) PARTITION BY RANGE(d) (
       foo_partition_01(STARTING MINIMUM ENDING 2008-12-31 TABLESPACE mytablespace),
       foo_partition_02(STARTING 2009-01-01 ENDING 2009-12-31),
       foo_partition_03(STARTING 2010-01-01 ENDING 2010-12-31)
   );

Partition an existing non-partitioned table: 

   ALTER TABLE s.foo PARTITION BY LIST(column_name) (                                                    
       foo_partition_01(VALUES(AT DE) TABLESPACE mytablespace),                               
       foo_partition_02(VALUES(FR US))
   );

Add new partitions: 

   ALTER TABLE s.foo ADD PARTITION (
       foo_partition_04 (STARTING 2011-01-01 ENDING 2011-12-31),
       foo_partition_05 (STARTING 2012-01-01 ENDING 2012-12-31) TABLESPACE mytablespace)
   );

More details on how to use all features of pgEasyPart can be found in the USAGE file.

Documentation

Original version

  • Documentation of the source code (javadoc) can be found in the pgEasyPart/doc folder.
  • Usage: README | USAGE
  • For more information on pgEasyPart, partitioning, (…): [master thesis] (language: German)

Updated version

Sources

You can checkout pgEasyPart from subversion: 

 git clone git://git.semanticlab.net/oss/pgEasyPart

License

pgEasyPart - a tool that supports partitioning in PostgreSQL databases

released under The PostGreSQL License

Copyright (c) 2010, Daniel Sturm (original author) Copyright (c) 2010, Heinz-Peter Lang (maintainer)

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL Daniel Sturm or Heinz-Peter Lang BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF Daniel Sturm HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Daniel Sturm or Heinz-Peter Lang SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND Daniel Sturm HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Retrieved from "http://www.semanticlab.net/index.php/PgEasyPart"
Personal tools