{"id":46566,"date":"2018-03-27T00:00:00","date_gmt":"2018-03-27T07:00:00","guid":{"rendered":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/blog\/migrating-from-postgresql-to-griddb\/"},"modified":"2025-11-13T12:54:33","modified_gmt":"2025-11-13T20:54:33","slug":"migrating-from-postgresql-to-griddb","status":"publish","type":"post","link":"https:\/\/www.griddb.net\/en\/blog\/migrating-from-postgresql-to-griddb\/","title":{"rendered":"Migrating from PostgreSQL to GridDB"},"content":{"rendered":"

Introduction<\/h2>\n

In one of our past posts<\/a>, we migrated the relational database MySQL using Java. In this post we will continue a similar trend by showing step-by-step how to migrate an existing PostgreSQL database<\/a> to a GridDB cluster with Python.
\nPostgresql<\/a> is a relational database that has been widely used in business intelligence, web development, commerce, healthcare, and biopharmacy. While PostgreSQL offers several benefits, it still suffers from the same disadvantages as many other relational databases. Some of these include poor scalability and a lack of flexibility. PostgreSQL can also have a steep learning curve and have worse performance when handling certain datasets.
\nGridDB on the other hand will provide the great horizontal scalability<\/a> and flexibility of a NoSQL database. It also provides ACID compliance<\/i> along with high reliability<\/a> and availabilty<\/a>. GridDB also gives superior performance with an in-memory architecture, making it perfect for many applications, namely IoT applications.<\/p>\n

Use Case<\/h3>\n

To make things simple, we will use the same industry example as our last migration. The scenario will be a photovoltaic<\/b> site <\/a> (PV) which can be thought of as an industrial solar farm<\/b>. Most of the data in this database will relate to information and measurements sent by sensors and the facilities they are contained in.<\/p>\n

GridDB Data Model<\/h3>\n

Since PostgreSQL is a relational<\/i> database very similar to Oracle Database and MySQL, we will be using the same example database as last time. Therefore our schema for the PostgreSQL database will be the same. Our GridDB data model will also be the same as before, only that we will be creating and populating our database using a Python API<\/b>.<\/p>\n

There will be 4 tables<\/b> in the PostgreSQL database which will translate into 4 container schemas<\/b> in GridDB. They are detailed below.<\/p>\n

    \n
  1. Facilities:<\/b> Contains information and specifications for the facilities of the PV site.<\/li>\n

    <\/p>\n

    \n
    \n
    PostgreSQL Facilities Table<\/b><\/div>\n

    <\/p>\n

    \nCREATE TABLE IF NOT EXISTS facilities (\n\tfacilityId VARCHAR(20) PRIMARY KEY,\n\tname VARCHAR(50) NOT NULL,\n\tspecifications BYTEA\n);\n           <\/pre>\n<\/p><\/div>\n
    \n
    GridDB Facility Container Schema<\/b><\/div>\n
    <\/p>\n
    \nfacilities = gridstore.put_container(\"facilities\",\n                        [(\"facilityId\",griddb.GS_TYPE_STRING),\n\t\t\t(\"name\",griddb.GS_TYPE_STRING),\n                        (\"specifications\",griddb.GS_TYPE_BLOB)],\n\t\t\tgriddb.GS_CONTAINER_COLLECTION)\n           <\/pre>\n<\/p><\/div>\n
     <\/div>\n<\/p><\/div>\n
  2. Sensor:<\/b> Stores general information like sensor-type<\/i> and name on the PV site’s sensors<\/li>\n
    <\/p>\n
    \n
    \n
    PostgreSQL Sensor Table<\/b><\/div>\n
    <\/p>\n
    \nCREATE TABLE IF NOT EXISTS sensors (\n\tsensorId VARCHAR(40) PRIMARY KEY,\n\tfacilityId VARCHAR(20) REFERENCES facilities(facilityId)\n           ON DELETE CASCADE ON UPDATE CASCADE,\n\tname VARCHAR(50) NOT NULL,\n\ttype VARCHAR(50) NOT NULL\n);\n          <\/pre>\n<\/p><\/div>\n
    \n
    GridDB Sensor Container Schema<\/b><\/div>\n
    <\/p>\n
    \nfacility = gridstore.put_container(\"facility_1\",\n                [(\"sensorId\",griddb.GS_TYPE_STRING),\n\t\t(\"name\",griddb.GS_TYPE_STRING),\n                (\"type\",griddb.GS_TYPE_STRING)],\n                 griddb.GS_CONTAINER_COLLECTION)\n           <\/pre>\n<\/p><\/div>\n
     <\/div>\n<\/p><\/div>\n
  3. Readings:<\/b> Stores the timestamps and values<\/i> of the measurements recorded by the PV site’s sensors<\/li>\n
    <\/p>\n
    \n
    \n
    PostgreSQL Reading Table<\/b><\/div>\n
    <\/p>\n
    \nCREATE TABLE IF NOT EXISTS readings (\n\trecordId SERIAL PRIMARY KEY,\n\tsensorId VARCHAR(40) REFERENCES sensors(sensorId)\n            ON DELETE CASCADE ON UPDATE CASCADE,\n\tts TIMESTAMP DEFAULT NOW() NOT NULL,\n\tvalue REAL NOT NULL,\n\tstatus VARCHAR(255) NOT NULL\n);\n            <\/pre>\n<\/p><\/div>\n
    \n
    GridDB Reading Container Schema<\/b><\/div>\n
    <\/p>\n
    \ntimeseries = gridstore.put_container(\"sensor_1\",\n                [(\"timestamp\",griddb.GS_TYPE_TIMESTAMP),\n\t\t(\"value\",griddb.GS_TYPE_DOUBLE),\n                (\"status\",griddb.GS_TYPE_STRING)],\n\t\tgriddb.GS_CONTAINER_TIME_SERIES)\n            <\/pre>\n<\/p><\/div>\n
     <\/div>\n<\/p><\/div>\n
  4. Alerts:<\/b> Stores information related to alerts and notifications<\/i> sent by the sensors<\/li>\n
    <\/p>\n
    \n
    \n
    PostgreSQL Alerts Table<\/b><\/div>\n
    <\/p>\n
    \nCREATE TABLE IF NOT EXISTS alerts (\n\talertId SERIAL PRIMARY KEY,\n\tts TIMESTAMP DEFAULT NOW() NOT NULL,\n\tfacilityId VARCHAR(20) REFERENCES facilities(facilityId)\n            ON DELETE CASCADE ON UPDATE CASCADE,\n\tsensorId VARCHAR(40) REFERENCES sensors(sensorId)\n            ON DELETE CASCADE ON UPDATE CASCADE,\n\tlevel INT NOT NULL,\n\tdetail VARCHAR(255) NOT NULL\n);\n            <\/pre>\n<\/p><\/div>\n
    \n
    GridDB Alerts Container Schema<\/b><\/div>\n
    <\/p>\n
    \nalerts_col = gridstore.put_container(\"alerts\",\n                [(\"id\",griddb.GS_TYPE_INTEGER),\n\t\t(\"timestamp\",griddb.GS_TYPE_TIMESTAMP),\n                (\"facilityId\",griddb.GS_TYPE_STRING),\n\t\t(\"sensorId\",griddb.GS_TYPE_STRING),\n                (\"level\",griddb.GS_TYPE_INTEGER),\n                (\"detail\",griddb.GS_TYPE_STRING)],\n\t\tgriddb.GS_CONTAINER_COLLECTION)\n            <\/pre>\n<\/p><\/div>\n
     <\/div>\n<\/p><\/div>\n<\/ol>\n
    Performing the Migration<\/h3>\n
    Now that we have our schemas and data models setup for both PostgreSQL and GridDB, we can now perform our data migration. We will SELECT<\/code> all the rows<\/i> from each PostgreSQL table, reformat and rearrange into a GridDB Row<\/code>. To follow, we will then insert that row into its corresponding Collection<\/code> or Timeseries<\/code> container.
    \nFor reference, the PostgreSQL database being migrated is named 'pv'<\/code>.<\/p>\n
    Installing the Python APIs and Connectors<\/h4>\n
    First off, we will need the psycopg2 package<\/a> to use Python with PostgreSQL. We will also need the griddb_python_client package<\/a> to access GridDB from Python. You can install both of these packages using the pip<\/code> package installer.<\/p>\n
    \n$ python -m pip install psycopg2\n$ python -m pip install griddb_python_client\n<\/pre>\n
    Once installed we can connect to our pv<\/i> database in PostgreSQL using pscycopg2<\/a>. We will connect to our GridDB cluster with Python<\/a>.<\/p>\n
    Migrating the Facilities Table<\/h4>\n
    Once we have our Collection<\/code> container formed in GridDB for storing facilities, we can then access and migrate all the rows from the facilities<\/b> table in PostgreSQL. To begin, we obtain a row cursor<\/code> from the facilities table in PostgreSQL. A row cursor<\/code> returns an array<\/b> of one row’s column values<\/b>.
    \nAll the column values are translated to their appropriate types<\/i> in Python. This means no additional parsing or type-conversion of the values is necessary. All the individual values of the row can be obtained as long as you create the correct amount of variables<\/i> for the number of columns<\/b> returned for each row.
    \nOnce we have all the row’s column values, we can create a GridDB row-object and set all the row fields<\/code> accordingly. Type conversion between the databases is handled this way for types like strings and numerics. The only thing to note is that the BLOB<\/code> value from PostgreSQL should be converted to a bytearray<\/code> before being inserted into the GridDB     collection<\/a>.<\/p>\n
    \nquery = \"SELECT * FROM facilites\"\ncursor = connection.cursor() #Obtain row cursor from PostgreSQL connection\ncursor.execute(query)\nrow = cursor.fetchone()\nwhile row is not None:\n        ## Get all id, name, and specification column values from row\n        facility_id, name, blob = row\n        facility_row = facilities.create_row()\n        facility_row.set_field_by_string(0,facility_id)\n        facility_row.set_field_by_string(1,name)\n        facility_row.set_field_by_blob(2,bytearray(blob)) ## Convert Blob data to bytearray\n        ret = facilities.put_row(facility_row)\n<\/pre>\n
    Migrating Sensor Data<\/h4>\n
    During the migration of the facilities<\/i> table, we obtain the facility_id<\/code> of every facility in the database. With that facility id, we can create a sensor_collection<\/code> for that facility to store information on its sensors.
    \nFirst, we must get every row in the sensors<\/code> table in PostgreSQL that has the facility id<\/b>. Since our rows in our Sensor<\/b> containers in GridDB do not have facility_id<\/i> columns, there is no need to fetch those columns from PostgreSQL.
    \nNow that we have created our Collection container<\/i> for a facility’s sensors, we can go through the same process of parsing the PostgreSQL rows and inserting them into the facility’s Collection<\/i> in GridDB.<\/p>\n
    \nfacility = \"facility_1\"\nquery = \"SELECT sensorId,name,type FROM sensors WHERE facilityId='%s'\" % (facility)\ncursor = connection.cursor()\ncursor.execute(query)\nrow = cursor.fetchone()\nwhile row is not None:\n        sensor_id, name, sensor_type = row\n        sensor = sensor_collection.create_row()\n        sensor.set_field_by_string(0,sensor_id)\n        sensor.set_field_by_string(1,name)\n        sensor.set_field_by_string(2,sensor_type)\n        ret = sensor_collection.put_row(sensor)\n<\/pre>\n
    Migrating Sensor Readings<\/h4>\n
    Because we get the sensor_id<\/code> of each sensor when migrating the sensors<\/i> PostgreSQL table, we also create a Timeseries<\/code> container for that sensor to store the sensor’s recorded measurements. All that needs to be done is to SELECT<\/code> all the rows from the readings<\/b> table with that sensor-id<\/i>. Because timeseries<\/a> containers have timestamps as their row-keys<\/b>, we will order the rows obtained from PostgreSQL by the TIMESTAMP<\/code> column.
    \nJust with all the rows in all the past<\/i> tables, we go through the same process of retrieving all the column values so they can be set as row-fields<\/i> in a GridDB row.
    \nOne thing to note is that TIMESTAMP<\/code>, DATE<\/code>, and TIME<\/code> column values in PostgreSQL are returned as datetime<\/code> values in Python. Timestamp<\/b> columns, or row-fields, in GridDB, can only be set in a numerical<\/i> format. As a result, the datetime<\/i> object must be converted to a timestamp number. Once the conversion is complete, we can then set all the row-fields<\/i> and insert the row into the Timeseries<\/b> container for that sensor in GridDB.<\/p>\n
    \ngriddb = griddb_python_client\ndef datetime_to_timestamp(dt):\n        ## GridDB Timestamp Format: (All fields must be numerics)\n        ## $YEAR-$MONTH-$DAYT$HOUR:$MINUTE:$SECONDZ\n        time_string = dt.strftime(\"%Y-%m-%dT%XZ\") # '%X' means get timestamp value from 'datetime'\n        timestamp = griddb.Timestamp_parse(time_string)\n        return timestamp\n## (snip)\n## Migrate data from 'readings' table from PostgreSQL into Timeseries containers in GridDB\nsensor_id = \"sensor_1\" ## Example of a sensor id in a PV Site's facility\nquery = \"SELECT ts,value,status FROM sensors WHERE sensor_id = '%s' ORDER BY ts ASC\" % (sensor_id)\ncursor = connection.cursor()\ncursor.execute(query)\nrow = cursor.fetchone()\n## Obtain column values from each reading row (sensor measurement) from 'readings table\nwhile row is not None:\n        date, value, status = row\n        reading = timeseries.create_row()\n        timestamp = datetime_to_timestamp(date)\n        reading.set_field_by_timestamp(0,timestamp)\n        reading.set_field_by_double(1,value)\n        reading.set_field_by_string(2,status)\n        ret = timeseries.put_row(reading)\n<\/pre>\n
    Migrating the Alerts Table<\/h4>\n
    The alerts<\/code> table in PostgreSQL table will only be mapped to 1 collection container<\/b> in GridDB. As a result, the migration process will be similar to the facilities<\/b> table.<\/p>\n
    \nquery = \"SELECT * FROM alerts\"\ncursor = connection.cursor()\ncursor.execute(query)\nrow = cursor.fetchone()\nwhile row is not None:\n        alert_id, date, facility_id, sensor_id, level, detail = row\n        alert_notification = alerts_col.create_row()\n        timestamp = datetime_to_timestamp(date)\n        alert_notification.set_field_by_integer(0,alert_id)\n        alert_notification.set_field_by_timestamp(1,timestamp)\n        alert_notification.set_field_by_string(2,facility_id)\n        alert_notification.set_field_by_string(3,sensor_id)\n        alert_notification.set_field_integer(4,level)\n        alert_notification.set_field_by_string(5,detail)\n        ret = alerts_col.put_row(alert_notification)\n<\/pre>\n
    Conclusion<\/h2>\n
    Now that all 4 PostgreSQL tables<\/b> are migrated, our migration to GridDB is complete. From there, all the benefits from the superior design and performance of GridDB can be obtained and all the PostgreSQL data can be dropped. With GridDB containers, we get the SQL qualities like ACID compliance<\/a> but as well as NoSQL benefits like flexibility and high scalability<\/a>. All it takes is good design and proper data modeling to make the transition from relational databases to GridDB seamless.<\/p>\n
    Reference<\/h3>\n