Toad World Blog

Using Toad Edge Preview 2.0.0 with PostgreSQL 10 on Amazon RDS

Mar 27, 2018 11:40:00 AM by Deepak Vohra

Amazon Relational Database Service (RDS) is a managed service for relational databases. Not all relational databases are supported. PostgreSQL database is supported. The benefits of a managed service are that the infrastructure provisioning, database installation and configuration, backup and recovery, and snapshots are all performed automatically. Being an AWS service, RDS DB instances are integrated with the other AWS services, including CloudWatch for logging, IAM for authorization and authentication, and S3 for database backups. In this article we shall discuss using PostgreSQL database on RDS with Toad Edge Preview 2.0.0. This article has the following sections.

  • Setting the Environment
  • Creating an RDS DB Instance with PostgreSQL Engine
  • Enabling Connectivity in the RDS DB Instance
  • Obtaining Connection Parameters
  • Creating a Connection in Toad Edge
  • Creating a Database Table
  • Importing Table Data
  • Exporting Table Data to HTML Report
  • Deleting RDS DB Instance

 

Setting the Environment

Download and install Toad Edge Preview 2.0.0.

The only other prerequisite is an AWS account, which may be created at https://aws.amazon.com/resources/create-account/. Log in to the AWS Console at https://console.aws.amazon.com/ and select the RDS service. Click on Get started now on the RDS service page, which may also be accessed directly at https://console.aws.amazon.com/rds after logging into the AWS Console.

 

Creating an RDS DB Instance with PostgreSQL Engine

The first screen in the RDS service is for selecting an engine, as shown in Figure 1.

Figure 1. Select Engine

Select the PostgreSQL engine as shown in Figure 2 and click on Next.

Figure 2. Selecting PostgreSQL Service in RDS

Next, specify DB details for the PostgreSQL DB engine instance, as shown in Figure 3. Keep the default selection for License model as postgresql-license. Select the latest PostgreSQL version supported on RDS; PostgreSQL 10.1 R1. If only free tier features are to be used, select the checkbox Only enable options eligible for RDS Free Usage Tier. Toad Edge Preview 2.0.0 is presently certified with PostgreSQL 10.0.0 but may also be used with PostgreSQL 10.1. PostgreSQL 10.0.0 is not available on RDS.

Figure 3. Selecting PostgreSQL version as 10.1 in Specify DB Details

Select the default DB instance class db.t2.micro – 1 vCPU and 1 GiB RAM as shown in Figure 4. Multi AZ deployment is not available for the free tier. Storage type is General Purpose (SSD) and cannot be modified. The default Allocated storage for selected instance type is 20 GB.

Figure 4. Selecting Instance Class

Next, specify settings for the DB instance as shown in Figure 5. Specify a DB instance name (postgresdb), which must be unique for all the DB instances in an account for a region. Specify a Master Username and Master password. The requirements for DB instance name, Master Username and Password are indicated in the input dialog. Click on Next.

Figure 5. DB Instance Settings

Next, configure advanced settings for Network & Security, Database options, Encryption, Backup, Monitoring, and Maintenance as shown in Figure 6.

Figure 6. Configure Advanced Options

In Network & Security select a VPC and Subnet group and select Public accessibility as Yes as shown in Figure 7. Select Availability zone as No Preference. Select the option to Create new VPC security group.

Figure 7. Configuring Network & Security

In Database options specify a Database name (postgresdb) and select the default Database port of 5432 as shown in Figure 8. Select the default DB Parameter group and Option group. A DB parameter group contains the configuration for the database engine. An Option group specifies configuration for additional features or options of database engine.

Figure 8. Database Options

Encryption may not be supported, as indicated by a message in Figure 9. The default backup retention period is 7 days. Select Backup window as No preference.

Figure 9. Configuring Encryption and Backup

In Monitoring select Disable enhanced monitoring and in Maintenance select Enable auto minor version upgrade as shown in Figure 10. Click on Launch DB Instance.

Figure 10. Configuring Monitoring and Maintenance

The DB instance postgresdb starts to get created, as indicated in the status column in Figure 11.

Figure 11. DB Instance postgresdb Creating

The DB instance status gets updated and may also be refreshed with the refresh button periodically. The status is shown to be backing-up on the Instances page in Figure 12.

Figure 12. DB instance Status is backing-up

When the DB instance gets completed the Status becomes available, as shown in Figure 13.

Figure 13. DB Instance status available

Click on postgresdb in Instances to list the instance detail page, as shown in Figure 14.

Figure 14. DB Instance Detail Page 

 

Enabling Connectivity in the RDS DB Instance

By default the Security group associated with an RDS DB instance does not provide access from any IP outside the Subnet group of the RDS DB instance. Toad Edge Preview 2.0.0 on a local machine, for instance, won’t be able to connect with the RDS DB instance. To enable connectivity click on the Security groups link, which is listed in the Connect section on the DB Instance detail page and also in the Details section shown in Figure 15.

Figure 15. Security groups link

In the Security group console select the Inbound tab and click on Edit to add (or modify) an inbound rule to allow all traffic, as shown in Figure 16. Alternatively, an inbound rule may be added for a specific IP on which Toad Edge Preview 2.0.0 is installed.

Figure 16. Inbound Rule to allow All traffic

 

Obtaining Connection Parameters

We need connection parameters for the PostgreSQL host name, port and database. The host name is the Endpoint listed in the Connect section, as shown in Figure 17 on the DB instance detail page. The Port is also listed in the Connect section and is 5432 by default. Publicly accessible must be set to Yes.

Figure 17. Endpoint

The database name is the DB name specified when creating the DB instance in Database options in Figure 8 earlier. The DB Name may be obtained from the Details section on the DB instance page, as shown in Figure 18.

Figure 18. DB Name

 

Creating a Connection in Toad Edge

To create a connection select Connect>New Connection as shown in Figure 19.

Figure 19. Connect>New Connection

In the New Connection wizard select PostgreSQL database platform as shown in Figure 20 and click on Next. The other option is to select EnterpriseDB (EDB) Postgres, which is not supported on RDS.

Figure 20. Selecting Database Platform

In Connection Settings specify Hostname as the Endpoint copied in Figure 17. Specify Database as postgresdb. Specify the Username as the Master Username configured when creating the DB instance. Specify Password as the Master Password. The connection string gets listed. An option to specify a custom connection string is also provided but is not suitable for connecting to RDS. Click on Test Connection to test the connection as shown in Figure 21.

Figure 21. Connection Settings>Test Connection

The Connection is OK message gets displayed, as shown in Figure 22. Click on Next.

Figure 22. Connection is OK

In Connection Details select the option Enable Autocommit as shown in Figure 23. Click on Finish.

Figure 23. Connection Details>Finish

As PostgreSQL 10.0 is supported and certified but PostgreSQL 10.1 is not certified, the dialog Non-certified database gets displayed. as shown in Figure 24.

Figure 24. Non-certified Database

A connection gets created and added to the Connections view. as shown in Figure 25.

Figure 25. Connection added to Connections view

 

Creating a Database Table

To add a database table two options are available:

  • Use the New Table wizard, which is launched from the Object Explorer
  • Use the SQL Worksheet to run a DDL statement

We shall use a SQL Worksheet to run the following statement.

CREATE TABLE wlslog(timestamp VARCHAR(255),category VARCHAR(255),type VARCHAR(255),
servername VARCHAR(255), code VARCHAR(255),msg VARCHAR(255));

A Worksheet is associated with a connection by default, as shown in Figure 26. A new Worksheet may also be opened by selecting Open SQL Worksheet in the toolbar, also shown in Figure 26.

Figure 26. Open SQL Worksheet in toolbar and Worksheet associated with a connection

Add the DDL statement in the Worksheet and click on Execute SQL Statement as shown in Figure 27.

Figure 27. Execute SQL Statement

A new table wlslog gets created as indicated by the Create table processed message in Figure 28. Because AutoCommit is ON the DDL statement does not need to be committed explicitly.

Figure 28. Create table processed

The new table wlslog gets listed in the Object Explorer as shown in Figure 29.

Figure 29. Table wlslog in Object Explorer

Several table options are available for table manipulation; for which, right-click on the wlslog table in Object Explorer as shown in Figure 30. Some of the table options are Refresh, Open (in a Data Editor), Rename, Copy Table…, and Record Count.

Figure 30. Table Options

 

Importing Table Data

Three options are available to add table data:

  • Run DML SQL Statements in a SQL Worksheet
  • Add data in Data Editor with Insert Row
  • Import data from Object Explorer

Data may be imported from a CSV file or a SQL file. We shall import data from CSV; for which, create a file wlslog.csv and copy the following listing to the CSV file. The first row is for column headers and each row of data has CSV data for timestamp, category, type, servername, code and msg.

timestamp,category,type,servername,code,msg

Apr-8-2014-7:06:16-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to STANDBY    
Apr-8-2014-7:06:17-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to STARTING    
Apr-8-2014-7:06:18-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to ADMIN      
Apr-8-2014-7:06:19-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to RESUMING    
Apr-8-2014-7:06:20-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000331,Started WebLogic AdminServer      
Apr-8-2014-7:06:21-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to RUNNING      
Apr-8-2014-7:06:22-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000360,Server started in RUNNING mode

By default a new table is empty, as shown in the Data Editor for the wlslog table in Figure 31. To import data right-click on Tables in Object Explorer and select Import Data as shown in Figure 31.

Figure 31. Tables>Import Data

The Import Data wizard gets launched, as shown in Figure 32. For Import Format select CSV file or ZIP archive with CSV content. Click on the selector (…) for the Source field and select the wlslog.csv file. The Error Log file directory is the same as the wlslog.csv file directory by default. Click on Next.

Figure 32. Selecting Source and Input Format

In CSV Settings the detailed settings, including Destination Database Table, CSV Format Settings, and Data Format Settings are listed, as shown in Figure 33. Data Format Preview is also displayed. Click on Finish.

Figure 33. Import Data>CSV Settings

The data gets imported, as indicated in the Data Import Result dialog in Figure 34. The Refresh data in table (s) is selected by default and if de-selected the table data would need to be refreshed with the Refresh button. Click on Close.

Figure 34. Data Import Result

The Data tab in the Data Editor lists the data imported, as shown in Figure 35.

Figure 35. Data in Data Editor

The Columns tab displays the table columns including their data type and size and whether a column is Nullable, as shown in Figure 36.

Figure 36. Columns

 

Exporting Table Data to HTML Report

To export data to HTML select the row/s of data to export. Multiple rows are selected with Shift+Row. Right-click in the Data Editor and select Export Grid selection>HTML as shown in Figure 37.

Figure 37. Export Grid Selection>HTML

In Save As specify a file name and click on Save as shown in Figure 38.

Figure 38. Save As

The Data gets exported to an HTML file, which is shown in a browser in Figure 39.

Figure 39. HTML Report

 

Deleting RDS DB Instance

When not being used the RDS DB instance may be stopped by selecting the DB instance in the Instances table and Instance Actions>Stop. To delete a DB instance select Instance Actions>Delete as shown in Figure 40.

Figure 40. Instance Actions>Delete

In Delete DB Instance select Yes to create a final snapshot, as shown in Figure 41, and click on Delete.

Figure 41. Delete DB Instance

The DB instance starts to get deleted, as shown by the deleting message in Figure 42. Select Snapshots to list the snapshots.

Figure 42. DB Instance deleting

The PostgreSQL database snapshot Details is shown in Figure 43.

Figure 43. PostgreSQL Database Snapshot

 

Conclusion

In this article we discussed using Toad Edge Preview 2.0.0 with a PostgreSQL engine-based RDS DB instance on AWS. The benefit of using Toad Edge as a client is an integrated environment for connecting to PostgreSQL database and creating and browsing database objects with a wizard or tool each to create a table, add data, import table data, and export data.

Tags: Toad Edge PostgreSQL Development

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.