PostgreSQL docking sequoiadb

PostgreSQL is an open-source SQL database that supports standard SQL and allows users to connect to PostgreSQL via the JDBC driver for application development. The user extends the PostgreSQL function, allowing the developer to access the SEQUOIADB database using SQL statements to complete the SEQUOIADB database, delete, check, and change operations. This paper introduces how to extend PostgreSQL function to realize PostgreSQL docking sequoiadb.

1 Deploying PostgreSQL

This tutorial is a docking tutorial for PostgreSQL and sequoiadb, so the authors recommend that users install and use PostgreSQL under Sdbadmin User (SEQUOIADB database Default User) (the version of PostgreSQL used in this tutorial is 9.3.4).

1) Source compilation PostgreSQL

Download Link: http://www.postgresql.org/ftp/source/

Compile and install after decompression (requires root permission)

$> tar -zxvf postgresql-9.3.4.tar.gz
$> cd postgresql-9.3.4/
$> ./configure && make && make install

2) Switch User

$>su-sdbadmin

3) Copy PostgreSQL file

$>cp-rf/usr/local/pgsql ~/

4) Enter the PostgreSQL directory

$>CD Pgsql

5) environment variable add PostgreSQL lib library

$>export ld_library_path=$ (pwd)/lib:${ld_library_path}

It is recommended that users add PostgreSQL lib to sdbadmin user's environment variables, otherwise each login sdbadmin use PostgreSQL, they need to be manually added PostgreSQL Lib to Ld_library_path

$> echo "Export ld_library_path=$ (PWD)/lib:${ld_library_path}" >> ~/.bash_profile

6) Create a data directory for PostgreSQL

$>mkdir Pg_data

7) Initialize Data directory (This operation can only be done once)

$>bin/initdb-d pg_data/

1.2 Installing the Sequoiadb-postgresql plug-in

**1)

Create a lib directory for PostgreSQL * *

Get the Libdir path for PostgreSQL

$> pglibdir=$ (Bin/pg_config--libdir)

If the displayed Libdir directory does not exist, the user will need to manually create the directory themselves

$> mkdir-p ${pglibdir}

2) Create extension directory for PostgreSQL

Get the Sharedir path for PostgreSQL

$> pgsharedir=$ (Bin/pg_config--sharedir)

Re-create the Extemsion directory on the Shardir directory

$> mkdir-p ${pgsharedir}/extension

3) Copy the PostgreSQL extension file from the SEQUOIADB installation package

Copy sdb_fdw.so files from sequoiadb installed PostgreSQL directory to PostgreSQL lib directory, sequoiadb default installation directory is/OPT/SEQUOIADB

$> cp-f/opt/sequoiadb/postgresql/sdb_fdw.so ${pglibdir}

4) Copy the Sdb_fdw.control and Sdb_fdw--1.0.sql scripts to the extension directory, and two scripts need to be manually edited by the user

$> cp -f sdb_fdw.control ${PGSHAREDIR}/extension/ ;
$> cp -f sdb_fdw--1.0.sql ${PGSHAREDIR}/extension/ ;

Sdb_fdw.control Script Content

 # sdb_fdw extension
    comment = ‘foreign data wrapper for SequoiaDB access‘
    default_version = ‘1.0‘
    module_pathname = ‘$libdir/sdb_fdw‘
    relocatable = true

Sdb_fdw--1.0.sql Script Content

/* contrib/mongo_fdw/sdb_fdw--1.0.sql */
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION sdb_fdw" to load this file. \quit
CREATE FUNCTION sdb_fdw_handler()
RETURNS fdw_handler
AS ‘MODULE_PATHNAME‘
LANGUAGE C STRICT;
CREATE FUNCTION sdb_fdw_validator(text[], oid)
RETURNS void
AS ‘MODULE_PATHNAME‘
LANGUAGE C STRICT;
CREATE FOREIGN DATA WRAPPER sdb_fdw
HANDLER sdb_fdw_handler
VALIDATOR sdb_fdw_validator;

1.3 Deployment of PostgreSQL

1)

Check if the port is occupied

PostgreSQL Default boot port is "5432", check whether the port is occupied (check operation is recommended to use root user action, only check the port requires root permission, the remaining operations will need to operate under the Sdbadmin user)

$>netstat-nap | grep 5432

If Port 5432 is occupied or if you want to modify the boot port of PostgreSQL, execute:

$> sed-i "s/#port = 5432/port = 11780/g" pg_data/postgresql.conf

2) Start the PostgreSQL service process (need to use Sdbadmin user to execute the following command)

$> bin/postgres-d pg_data/>> logfile 2>&1 &

3) Check that PostgreSQL is started successfully

Execute command:

$> Netstat-nap | grep 5432

The result is:

tcp   0   0 127.0.0.1:5432     0.0.0.0:*         LISTEN     20502/postgres
unix  2   [ ACC ]   STREAM    LISTENING   40776754 20502/postgres     /tmp/.s.PGSQL.5432

4) Create database for PostgreSQL

$> bin/createdb-p 5432 foo

5) Enter the PostgreSQL shell environment

$> bin/psql-p 5432 foo

2 PostgreSQL Connection sequoiadb

The following operations are performed in the PostgreSQL shell environment

2.1 PostgreSQL is associated with SEQUOIADB

1) Load SEQUOIADB Connection driver

foo=# Create extension SDB_FDW;

2) configuration and SEQUOIADB connection parameters

foo=# create server sdb_server foreign data wrapper sdb_fdw options(address ‘192.168.30.182‘, service ‘11810‘, user ‘sdbadmin‘, password ‘cmbc123‘);

3) Set space and set of associated SEQUOIADB

Note: The collection space and collection must already exist in SEQUOIADB, or the query will be faulted.

By default, the table field is mapped to lowercase characters in sequoiadb, and if you force the specified field to be uppercase, create a reference note for the method 1

Mapping sequoiadb array types, creating a reference Note 2

foo=# Create foreign table test (name text, ID numeric) server sdb_server options (collectionspace ' Chen ', Collection ' Te St ');

4) Query

foo=# select * from test;

5) Write Data

foo=# INSERT into test values (' one ', 3);

6) Change data

foo=# Update test set id=9 where name= ' one ';

7) View all the tables (show tables;)

foo=# \d

8) View the description information of the table

foo=# \d Test

9) Delete Table

foo=# drop foreign table test;

10) exit the PostgreSQL shell environment

foo=# \q

2.2 Notes on Use

2.2.2 Precautions

1) Note the case of the character

The collection space, collection, and field names in sequoiadb are case-sensitive to the letter

Collection space, collection name uppercase

Suppose there is a collection space named Test in Sequoiadb, a collection of Chen, and a corresponding mapping table in PostgreSQL

foo=# Create foreign table SDB_UPCASE_CS_CL (name text) server Sdb_server options (collectionspace ' TEST ', collection ' CH EN ');

Field name uppercase

Suppose there is a collection space named Foo in the Sequoiadb, a collection of bar, and the data is saved as:

{
 "_id": {
   "$oid":"53a2a0e100e75e2c53000006"
 },
 "NAME": "test"
}

Establishing the corresponding mapping table in PostgreSQL

foo=# Create foreign table Sdb_upcase_field ("NAME" text) server Sdb_server options (collectionspace ' foo ', collection ' B Ar ');

To execute a query command:

foo=# select * from Sdb_upcase_field;

The query results are:

NAME------Test (1 rows)

2) mapping data types in SEQUOIADB

Suppose that there is a Foo collection space in Sequoiadb, a bar collection, and a record saved as:

{
 "_id": {
    "$oid":"53a2de926b4715450a000001"
 },
 "name": [
   1,
   2,
   3
 ],
 "id": 123
}

Establishing the corresponding mapping table in PostgreSQL

foo=# Create foreign table Bartest (name numeric[], ID numeric) server sdb_server options (collectionspace ' foo ', collect Ion ' Bar ');

To execute a query command:

foo=# select * from Bartest;

Query Result:

 name  | id
---------+-----
{1,2,3} | 123

3) Connection SEQUOIADB Coordination node error

If the SEQUOIADB coordination node of the PostgreSQL connection is restarted, the query times are wrong

Error:unable to get Collection "Chen.test", rc = -15hint:make sure the Collectionspace and collection exist on the Remot E database

Workaround:

Exiting the PostgreSQL shell

foo=# \q

Re-entering the PostgreSQL shell

$> bin/psql-p 5432 foo  

2.2.3 Adjusting the PostgreSQL configuration file

1) View the default configuration in Pg_shell

Execute command:

Foo=#\set

The result is:

AUTOCOMMIT = ‘on‘
PROMPT1 = ‘%/%R%# ‘
PROMPT2 = ‘%/%R%# ‘
PROMPT3 = ‘>> ‘
VERBOSITY = ‘default‘
VERSION = ‘PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit‘
DBNAME = ‘foo‘
USER = ‘sdbadmin‘
PORT = ‘5432‘
ENCODING = ‘UTF8‘

2) When adjusting Pg_shell query, get the number of records each time

Foo=#\set Fetch_count 100

Adjust to get 100 records each time Ps_shell returns records immediately before continuing to get.

Modify the configuration file directly in Pg_shell, only in the current Pg_shell, re-login Pg_shell need to be reset.

3) Modify the configuration file, adjust the Pg_shell query, get the number of records each time

Execute command:

$> ${pg_home}/bin/pg_config-sysconfdir

The result is:

/opt/sequoiadb/pgsql/etc

If the display directory does not exist, you can create it manually

$> mkdir-p/opt/sequoiadb/pgsql/etc

Write parameters that need to be modified to the configuration file

$>echo "\\set fetch_count" >>/OPT/SEQUOIADB/PGSQL/ETC/PSQLRC

4) Adjust the log level of the Pg_shell

$>sed-i ' s/#client_min_messages = notice/client_min_messages = Debug1/g ' pg_data/postgresql.conf

5) Adjust the log level of the PG engine

$>sed-i ' s/#log_min_messages = warning/log_min_messages = Debug1/g ' pg_data/postgresql.conf

3 connecting PostgreSQL using a Java client

1) Modify the listener address of PostgreSQL

$>sed-i "s/#listen_addresses = ' localhost '/listen_addresses = ' 0.0.0.0 '/g ' pg_data/postgresql.conf

2) Modify the list of trusted machines

$>linenum=$ (cat-n pg_data/pg_hba.conf | grep "# IPV4 Local connections:" | awk ' {print '} '); Let "linenum=linenum+1", varstr= "host   all            0.0.0.0/0              Trust"; sed-i "${linenum} a${varstr}" Pg_ data/pg_hba.conf;

3) Restart PostgreSQL

$>bin/pg_ctl Stop-s-D pg_data/-M fast; bin/postgres-d pg_data/>> logfile 2>&1 &

3.2 JDBC Connection Program

package com.sequoiadb.sample;
import java.sql.*;
public class postgresql_sample {
   static{
     try {
       Class.forName"org.postgresql.Driver");
     } catch (ClassNotFoundException e) {
       e.printStackTrace();
     }
   }
   public static void main( String[] args ) throws SQLException{
    String pghost = "192.168.30.182";
    String port = "5432";
    String databaseName = "foo";
    // postgresql process is running in which user
    String pgUser = "sdbadmin";
    String url = "jdbc:postgresql://"+pghost+":"+port+"/" + databaseName;
    Connection conn = DriverManager.getConnection(url, pgUser, null);
    Statement stmt = conn.createStatement();
    String sql = "select * from sdb_upcase_field ";
    ResultSet rs = stmt.executeQuery(sql);
    boolean isHeaderPrint = false;
    while (rs.next()) {
       ResultSetMetaData md = rs.getMetaData();
       int col_num = md.getColumnCount();
       if (isHeaderPrint){
         for (int i = 1; i <= col_num; i++) {
           System.out.print(md.getColumnName(i) + "|");
           isHeaderPrint = true;
         }
       }
       for (i = 1; i <= col_num; i++) {
         System.out.print(rs.getString(i) + "|");
       }
       System.out.println();
     }
     stmt.close();
     conn.close();
   }
}


 

	
  


 

• This article is from: Linux Learning Tutorial Network

PostgreSQL docking sequoiadb