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

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. Prefix the URL with s3: for an S3-compatible bucket or file: for a local file system.

select pgfs.create_storage_location('my_file_storage', 'file:///tmp/my_path',  NULL,  '{}'::json,    '{}'::json );

When using the file: schema, provide an absolute path, one that starts with /, for example /tmp/my_path). Together with the schema indicator file://, there are then three slashes at the beginning of the path.

The function also takes an optional msl_id parameter, which isn't used. It also requires options and credentials parameters. If those are unused, you must pass them as empty JSON objects.

Creating a storage location with options and credentials

Using the options and credentials parameters allows a range of other settings to be passed.

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 options and credentials is that options remain visible to users querying the extension while 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've 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 need to create a volume from the storage location. To do that, see Creating a volume.

Listing storage locations

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

select * from pgfs.list_storage_locations();

This command returns a table of currently defined storage locations. Credentials are shown only if the user has the necessary permissions. Otherwise the column is 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 command returns the details of the storage location named my_storage.

Updating a storage location

You can 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 command removes the storage location named my_storage from the database.