PGFS functions for Pipelines

Using the PGFS functions

The PGFS extension provides a set of functions to create and manage storage locations.

Creating a storage location

We start with creating a storage location. A storage location is a reference to a location in an external file system. You can create a storage location with the pgfs.create_storage_location function.

select pgfs.create_storage_location('my_storage', 's3://my_bucket','','{}'::JSONB,'{}'::JSONB);

The create_strorage_location function takes a name for the storage location and then a URL for the location. The URL should be prefixed with s3: for an S3-compatible bucket or file: for a local file system.

The function also takes an optional msl_id parameter, which is not used. The function also takes an options parameter and credentials parameter. They are not optional and should be passed as empty JSON objects if not used.

Creating a storage location with options and credentials

Using the options and credentials parameters allows the passing of a range of other settings.

The options parameter is a JSON object that can be used to pass additional options to the storage location. The credentials parameter is a JSON object that can be used to pass credentials to the storage location.

The difference between option and credentials is that while options remains visible to users querying the extension, credentials are hidden to all users except superusers and the user that creates the storage location.

For example, you can create a storage location with options and credentials like this:

select pgfs.create_storage_location('my_storage', 's3://my_private_bucket', null, '{"region": "eu-west"}'::JSONB, '{"access_key_id": "youraccesskeyid", "secret_access_key":"yoursecretaccesskey"}'::JSONB);

Once you have created a storage location, you can use it to create foreign tables and access files in the external file system. To use it with aidb, you will need to create a volume from the storage location. To do that see creating a volume documentation.

Listing storage locations

You can list all storage locations with the pgfs.list_storage_locations function.

select * from pgfs.list_storage_locations();

This will return a table of currently defined storage locations. Credentials will only be shown if the user has the necessary permissions, otherwise the column will be NULL.

Getting a storage location

You can get the details of a specific storage location with the pgfs.get_storage_location function.

select * from pgfs.get_storage_location('my_storage');

This will return the details of the storage location with the name my_storage.

Updating a storage location

You can also update a storage location with the pgfs.update_storage_location function.

select pgfs.update_storage_location('my_storage', 's3://my_bucket', null, '{"region": "eu-west"}'

Deleting a storage location

You can delete a storage location with the pgfs.delete_storage_location function.

select pgfs.delete_storage_location('my_storage');

This will remove the storage location with the name my_storage from the database.