Creating a cluster, connecting, and creating a database

Follow this quick walkthrough to configure a Postgres cluster on BigAnimal.

Step 1: Create an account and sign into the portal

If you haven't done so already, you'll need to create your EDB account.

Then, use your newly created account to access the BigAnimal portal.

Step 2: Create a cluster

On the overview page, select Create New Cluster.
You should now find yourself at the Create Cluster page.
Select the options for your cluster. See Creating a cluster for more information.

Managing your cluster

After you select Create Cluster, you return to the Clusters page with your newly configured cluster now populating the list. From the Clusters page, you can view the details, create a replica of your cluster, and edit and delete your cluster.

Step 3: Connect to your new cluster

Select your cluster to get an overview of how it has been configured. Select the Connect tab to see more information about how to connect to your cluster.

Select the Overview tab and copy the Quick Connect command. Paste it into a terminal where psql is installed. It will prompt for your password and put you on a SQL command line. For example:

psql -W "postgres://edb_admin@p-qzwv2ns7pj.pg.biganimal.io:5432/edb_admin?sslmode=require"
Password:

Output

psql (15.2) 
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

edb_admin=>

Note

While psql is a good all-around option for working with Postgres databases, you can use the client of your choice. See Connect to a cluster for more ideas.

Things to try

Create a new database

We're going to create some sample math data, so we're going to create a database called math. We could use the default edb_admin database, but best practice is to isolate data.

Create a new math database.

create user math with password 'math_password';
create database math;

Grant the math role to edb_admin.
```
grant math to edb_admin;
```
Connect to the math database. You're prompted for the edb_admin password you provided in Step 2.
```
\connect math
```

Populate a table and query it

We're going to use temporary tables to calculate prime numbers using a Sieve of Eratosthenes.

Create a table called primes for storing prime numbers.

CREATE TABLE primes (
  num INTEGER,
  PRIMARY KEY (num)
);

Populate the table with all prime numbers up to 1000. (This code is based on code from David Fetter.)

-- Based on https://wiki.postgresql.org/wiki/Sieve_of_Eratosthenes

WITH RECURSIVE
t0(m) AS (
    VALUES(1000)
),
t1(n) AS (
    VALUES(2)
UNION ALL
    SELECT n+1 FROM t1 WHERE n < (SELECT m FROM t0)
),
t2 (n, i) AS (
    SELECT 2*n, 2
    FROM t1 WHERE 2*n <= (SELECT m FROM t0)
UNION ALL
    (
        WITH t3(k) AS (
            SELECT max(i) OVER () + 1 FROM t2
        ),
        t4(k) AS (
            SELECT DISTINCT k FROM t3
        )
        SELECT k*n, k
        FROM
            t1
        CROSS JOIN
            t4
        WHERE k*k <= (SELECT m FROM t0)
    )
)
INSERT INTO primes (
  SELECT n FROM t1 EXCEPT
  SELECT n FROM t2
  ORDER BY 1
);

Select the largest prime number less than 1000.
```
SELECT max(num)
FROM primes
WHERE num < 1000;
```

Creating a cluster, connecting, and creating a database

Step 1: Create an account and sign into the portal

Step 2: Create a cluster

Managing your cluster

Step 3: Connect to your new cluster

Note

Things to try

Create a new database

Populate a table and query it

Further reading

← Prev

↑ Up

Next →