How to Setup PostgreSQL Server for Metabase
You can follow the “this tutorial” to create the ECS instance and steps to connect to your instance. This tutorial assumes that you have created your Alibaba instance and “192.168.0.101” is the public IP address assigned to your Ubuntu instance. You have also configured “metabase.example.com” to point to your Ubuntu instance. Once you have connected to your instance via SSH, run the following command to update the repository cache and the base system.
apt update && apt -y upgrade && apt -y autoremove
Setup ApsaraDB for PostgreSQL
By default, Metabase is configured to use H2 database. H2 database is a flat file based database and it does not require any special software to run. However, using an H2 database in the multi-user production environment is not recommended as it deteriorates the performance of the application. In this tutorial, we will use the PostgreSQL server from ApsaraDB for RDS to host the Metabase database. Go to “https://rdsnew.console.aliyun.com” and create a new RDS instance for PostgreSQL. Choose your subscription type and select region. Creating the RDS instance in the same region where your ECS instance is located gives you many leverages such as ECS and RDS instances can communicate using a private intranet address. We do not require to apply for an internet address and intranet data transfers are free of charge. For this tutorial, I have created the RDS instance in Mumbai region where my ECS instance is also located.
Choose DB Engine as PostgreSQL and select version 9.4. Select the zone and VPC where your ECS instance is located. Choose the type and capacity, for the start, you can choose 1GB instance with 5GB capacity. Instance type and capacity can be increased later on demand.
After your purchase of the instance, it will take a few minutes for the instance to be active. Once your instance is activated, click on “manage” link and switch to “Security” tab. Now add a new whitelist group, provide any descriptive name for the new group and enter the private or intranet IP address of the ECS instance. Since we have only whitelisted the ECS instance, the database server will be accessible only from our ECS instance.
Now, switch to “Accounts” tab and create a new master account for your PostgreSQL server with username “postgres”. The master account will be used to create new users and databases from PSQL shell.
Finally, go the connection options tab to find the intranet address and port assigned to your RDS instance. Make a note of the intranet address and the port as we will require it later in the tutorial.
Our PostgreSQL server instance is ready and now we can move on to the installation of Metabase.
For further information of the installation of Metabase, please go to this tutorial.
Related Blog Posts
In this article, you will get some information on the installation and the way for setting up a PostgreSQL database on Ubuntu using an Alibaba Cloud ECS instance.
Metabase is able to run queries over multiple types of database such as MySQL, PostgreSQL, Presto, MongoDB, MSSQL etc. Because of this feature, you can directly add your database into Metabase and ask questions from it. In a production environment, it is best to create a new user with read-only access just for the Metabase instance. If your database contains sensitive information, you can also create table views according to preferences to display only selected columns.
Related Market Product
Postgres Professional is a key contributor to PostgreSQL community.
At Postgres Professional we develop Postgres Pro Database, a private PostgreSQL fork.
Each new Postgres Pro Database version is the latest PostgreSQL database version with patches committed to PostgreSQL community and Postgres Pro extensions/patches which are open source in most cases.
Postgres Pro Database is available in both Standard and Enterprise editions.
Cloud Database AnalyticDB for PostgreSQL is fully compatible with the message protocols of PostgreSQL 8.2 and allows direct access to tools that support the PostgreSQL 8.2 message protocols such as libpq, JDBC, ODBC, psycopg2, pgadmin III, and so on.
This topic describes how to configure a PostgreSQL data source. The PostgreSQL data source allows you to read/write data on PostgreSQL, and supports configuring synchronization tasks in wizard and script mode.
ApsaraDB HybridDB for PostgreSQL is an online MPP (Massively Parallel Processing) data warehousing service based on the open source Greenplum Database.
ApsaraDB HybridDB provides online expansion and performance monitoring service to free your team from complicated MPP cluster operations and management (O&M). This enables database administrators, developers and data analysts to focus on upgrading enterprise productivity through SQL development.
DataWorks is a Big Data platform product launched by Alibaba Cloud. It provides one-stop Big Data development, data permission management, offline job scheduling, and other features.
DataWorks works straight ‘out-the-box’ without the need to worry about complex underlying cluster establishment and Operations & Management.