ElephantSQL offer tools to simplify the usage of the PostgreSQL database. Various monitoring tools will help you to overview server metrics, backups are handled automatically and we make it very easy to do Point in time recovery. The instance details, such as connection URL, statistics, open connections and your slow queries can be seen at the details pages in the Control Panel. Here you will also be able to restore backups and rotate your password. If you are on a dedicated plan (Happy Hippo or larger) you will be able to view server metrics, you can set up followers and you can view information for all your databases.

SQL Browser

ElephantSQL provides a browser tool for SQL queries where you can create, read, update and delete data direct from your web browser. A link to the browser can be found on the console page for your instance. The different available tables for the database can be found under the tables button to the right.

sql browser

Other SQL tools

Other tools that can be used when handling SQL data and your database instance are pgAdmin, and psql. pgAdmin is a graphical administration client for PostgreSQL and psql is a terminal-based tool for work with PostgreSQL.

psql

Server metrics

For dedicated plans only

ElephantSQL offers various monitoring tools. These tools will address performance issues promptly and automatically, before they impact your business. ElephantSQL monitoring includes diagrams for CPU, disk and memory usage.

CPU Usage

CPU Usage refers to how much work your processor is doing.

postgres server metrics
  • I/O Wait:

    Show percentage of time spent by the CPU waiting for a IO (input/output) operation to complete, the percentage of time the CPU have to wait on the disk.
  • User time:

    Show percentage of time your program spends executing instructions in the CPU. In this case, the time the CPU spent running PostgreSQL.

    If this is high it probably means you are on the limit of what your server can handle. You should consider upgrading before lack of CPU power becomes a serious issue.

  • System time:

    Describes percentage of time the CPU spent running OS tasks.

  • Steal time:

    Percentage of CPU time "stolen" by the virtualization system - time spent when the virtual CPU waits for a real CPU. If this is high it means that you are using to much CPU power. This can seriously impacting the performance of your server. You should probably upgrade to a larger instance.

Memory Usage

  • Used: Percentage of used memory.
  • Free: Percentage of free memory.

Disk Usage

  • Used: Percentage of used disk space
  • Free: Percentage of free disk space

postgres server metrics

Backups

Automated backups are performed every day. These are stored in a cloud file storage so they are always accessible to you (same cloud as your instance). The last month backup files can be found and downloaded in the ElephanSQL Control Panel.

postgres backup

The backups are compressed with lzop, to restore the data you can usually use the following: lzop -cd "$FILE_NAME" | psql "$DATABASE_NAME"

For dedicated server: Full backups of the whole server is performed every week.

Backup API

A backup API is available, that allows you to list, create, and restore from backups programmatically.

Database statistics

postgres database statistics

Cache Hit Rate

Usually (in many applications) only a fraction of the data is regularly accessed. Postgres is tracking access patterns of data and will on its own keep frequently accessed data in cache. Cache hit rate show how many of your indexes that are within your cache. Generally you want your database to have a cache hit rate of about 99%.

Index usage

If you’re not somewhere around 99% on any table over 10,000 rows you may want to consider adding an index. Indexes are most valuable across very large tables. When examining where to add an index you should look at what kind of queries you’re running and add indexs to some other id or on values that you’re commonly filtering.

Index Hit Rate

Show how many of your indexes that are within your cache. You should have an index hit rate around 99%, similar to your regular cache hit rate.

Connections

Open connections can be see in the tab open connections. You have the option to terminate a connection from the connection tab.

Slow queries

Identify which queries that are running slowly in your system by checking the slow queries tab. Inefficient SQL queries can kill the performance of your application.

postgres query

Point in time recovery

For dedicated plans only

Full backups of the whole server is performed every week and WAL backups are uploaded every minute to S3. These backups can be used for disaster recovery as well as for point-in-time-recovery.

postgres backup Followers

Followers (High Availability plans)

For dedicated plans only

On ElephantSQL you can always spread your data across multiple data centers and cloud providers. You can ensure that your data is always accessible and maximizing uptime by adding a follower to your database. A follower is a read-only copy of the master database but it can at any time be promoted to accept new writes and updates. It will be a hot-standby ready to take over if you main database crashes. When data is modified and committed on the master database the changes are streamed to the follower databases. You can view your followers and add followers from the Control Panel of your instance.

The follower will be promoted automatically by ElephantSQL in situations where the primary database becomes corrupted or unavailable. Failover is set to 30 seconds. Of course is manual failover also available. The follower to a database (in AWS) is physically located in a different availability zone (AZ), to protect against AZ-wide failures.

The follower will be available for read only queries. The address to the follower of your master node can be found in "details" for your instance (e.g. seasoned-mango-read.db.elephantsql.com) as in the picture below.

It's important to reconnect the clients when they are being disconnected during failover. During failover is the DNS records for the master and follower updated so make sure your clients create new connections if connection is lost, This is handled differently in different client libraries. Some libs need to create a new connection while other client libraries are handling the failover.

Follower setup Followers

Integrated monitoring services

For dedicated plans only

Logs are a critical part of any system, they give you insight into what a system is doing as well what happened. You're able to ship your PostgreSQL logs from ElephantSQL dedicated instances to Papertrail, Splunk, Loggly and Logentries.

Fork

For dedicated plans only

When you want to move your database to a different data center, migrate to a new cloud, or create a copy of all your data for test - ElephantSQL can easily help you create a standalone copy of your database.