🐍🐳🐘 A python command line interface for DigitalOcean postgres clusters (5+ integrations).
Uses click, pgcli, and rich to create beautiful command line interactions for postgres databases.
You can easily extend this framework to fit your particular use case.
Some things that DOpg can do include:
- query databases directly through a nice autocompletion widget
- use the DO databases API seamlessly in an intuitive and beautiful way (e.g. in tables and pretty printed json); cache api responses in
api_response.jsonin core folder so you don't need to log this elsewhere - work with doctl - if you prefer DO's command line interface (though we do recommend using the API)
- get postgres doctl and api docs from DigitalOcean for easy access
- get db pricing from public links - to make it easy to make purchasing decisions
This first version has only been tested on MacOS.
First, install python3 and doctl using homebrew:
brew install python@3.9, postgresql, doctl
Now that doctl is installed, initialize it with an auth token:
doctl auth init
Next, install python dependencies within a virtual environment:
git clone https://github.com/jim-schwoebel/dopg_cli.git
cd dopg_cli
virtualenv env
source env/bin/activate
pip3 install -r requirements.txt
Next, you can setup the cli (you will need your api key and must have already created a managed database postgres instance):
python3 cli.py
After this, you will see settings.json and databases.json in your dopg_cli directory used to configure all the commands that follow.
There are many ways to use the DOpg CLI client, sectioned out below.
python3 cli.py --command query
Connects with pgcli, a really nice interface to connect to postgres and query postgres databases (e.g. with autocompletion).
The DigitalOcean API is a great way to get json responses from common routes related to databases.
To get a tabular list of possible commands, follow:
python3 cli.py --command api --route help
And it should output api doc commands as of 2022-11-04 - something like:
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Route ┃ Description ┃ Method ┃ Sample ┃ Payload ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ add_connection_pool │ For PostgreSQL database clusters, connection pools │ POST │ curl -X POST -H 'Content-Type: application/json' -H │ {'name': 'backend-pool', 'mode': 'transaction', │
│ │ can be used to allow a database to share its idle │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ 'size': 10, 'db': 'defaultdb', 'user': 'doadmin'} │
│ │ connections. The popular PostgreSQL connection │ │ '$PAYLOAD' │ │
│ │ pooling utility PgBouncer is used to provide this │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ service. See here for more information about how │ │ │ │
│ │ and why to use PgBouncer connection pooling │ │ │ │
│ │ including details about the available transaction │ │ │ │
│ │ modes. To add a new connection pool to a PostgreSQL │ │ │ │
│ │ database cluster, send a POST request to │ │ │ │
│ │ /v2/databases/$DATABASE_ID/pools specifying a name │ │ │ │
│ │ for the pool, the user to connect with, the │ │ │ │
│ │ database to connect to, as well as its desired size │ │ │ │
│ │ and transaction mode. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ add_database_user │ To add a new database user, send a POST request to │ POST │ curl -X POST -H 'Content-Type: application/json' -H │ {'name': 'app-01'} │
│ │ /v2/databases/$DATABASE_ID/users with the desired │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ │
│ │ username. Note: User management is not supported │ │ '$PAYLOAD' │ │
│ │ for Redis clusters. When adding a user to a MySQL │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ cluster, additional options can be configured in │ │ │ │
│ │ the mysql_settings object. The response will be a │ │ │ │
│ │ JSON object with a key called user. The value of │ │ │ │
│ │ this will be an object that contains the standard │ │ │ │
│ │ attributes associated with a database user │ │ │ │
│ │ including its randomly generated password. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ add_new_database │ To add a new database to an existing cluster, send │ POST │ curl -X POST -H 'Content-Type: application/json' -H │ {'name': 'alpha'} │
│ │ a POST request to /v2/databases/$DATABASE_ID/dbs. │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ │
│ │ Note: Database management is not supported for │ │ '$PAYLOAD' │ │
│ │ Redis clusters. The response will be a JSON object │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ with a key called db. The value of this will be an │ │ │ │
│ │ object that contains the standard attributes │ │ │ │
│ │ associated with a database. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ configure_a_database_cluster_maintenance_window │ To configure the window when automatic maintenance │ PUT │ curl -X PUT -H 'Content-Type: application/json' -H │ {'day': 'tuesday', 'hour': '14:00'} │
│ │ should be performed for a database cluster, send a │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ │
│ │ PUT request to │ │ '$PAYLOAD' │ │
│ │ /v2/databases/$DATABASE_ID/maintenance. A │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ successful request will receive a 204 No Content │ │ │ │
│ │ status code with no body in response. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ create_a_new_database_cluster │ To create a database cluster, send a POST request │ POST │ curl -X POST -H 'Content-Type: application/json' -H │ {'name': 'backend-test', 'engine': 'pg', 'version': │
│ │ to /v2/databases. The response will be a JSON │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ '14', 'region': 'nyc3', 'size': 'db-s-2vcpu-4gb', │
│ │ object with a key called database. The value of │ │ '$PAYLOAD' │ 'num_nodes': 2, 'tags': ['production']} │
│ │ this will be an object that contains the standard │ │ 'https://api.digitalocean.com/v2/databases' │ │
│ │ attributes associated with a database cluster. The │ │ │ │
│ │ initial value of the database cluster's status │ │ │ │
│ │ attribute will be creating. When the cluster is │ │ │ │
│ │ ready to receive traffic, this will transition to │ │ │ │
│ │ online. The embedded connection and │ │ │ │
│ │ private_connection objects will contain the │ │ │ │
│ │ information needed to access the database cluster. │ │ │ │
│ │ DigitalOcean managed PostgreSQL and MySQL database │ │ │ │
│ │ clusters take automated daily backups. To create a │ │ │ │
│ │ new database cluster based on a backup of an │ │ │ │
│ │ existing cluster, send a POST request to │ │ │ │
│ │ /v2/databases. In addition to the standard database │ │ │ │
│ │ cluster attributes, the JSON body must include a │ │ │ │
│ │ key named backup_restore with the name of the │ │ │ │
│ │ original database cluster and the timestamp of the │ │ │ │
│ │ backup to be restored. Creating a database from a │ │ │ │
│ │ backup is the same as forking a database in the │ │ │ │
│ │ control panel. Note: Backups are not supported for │ │ │ │
│ │ Redis clusters. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ create_a_read_replica │ To create a read-only replica for a PostgreSQL or │ POST │ curl -X POST -H 'Content-Type: application/json' -H │ {'name': 'read-nyc3-01', 'region': 'nyc3', 'size': │
│ │ MySQL database cluster, send a POST request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ 'db-s-2vcpu-4gb'} │
│ │ /v2/databases/$DATABASE_ID/replicas specifying the │ │ '$PAYLOAD' │ │
│ │ name it should be given, the size of the node to be │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ used, and the region where it will be located. │ │ │ │
│ │ Note: Read-only replicas are not supported for │ │ │ │
│ │ Redis clusters. The response will be a JSON object │ │ │ │
│ │ with a key called replica. The value of this will │ │ │ │
│ │ be an object that contains the standard attributes │ │ │ │
│ │ associated with a database replica. The initial │ │ │ │
│ │ value of the read-only replica's status attribute │ │ │ │
│ │ will be forking. When the replica is ready to │ │ │ │
│ │ receive traffic, this will transition to active. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ delete_connection_pool │ To delete a specific connection pool for a │ DEL │ curl -X DELETE -H 'Content-Type: application/json' │ {} │
│ │ PostgreSQL database cluster, send a DELETE request │ │ -H 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ to /v2/databases/$DATABASE_ID/pools/$POOL_NAME. A │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ status of 204 will be given. This indicates that │ │ │ │
│ │ the request was processed successfully, but that no │ │ │ │
│ │ response body is needed. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ delete_database │ To delete a specific database, send a DELETE │ DEL │ curl -X DELETE -H 'Content-Type: application/json' │ {} │
│ │ request to /v2/databases/$DATABASE_ID/dbs/$DB_NAME. │ │ -H 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ A status of 204 will be given. This indicates that │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ the request was processed successfully, but that no │ │ │ │
│ │ response body is needed. Note: Database management │ │ │ │
│ │ is not supported for Redis clusters. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ destroy_a_database_cluster │ To destroy a specific database, send a DELETE │ DEL │ curl -X DELETE -H 'Content-Type: application/json' │ {} │
│ │ request to /v2/databases/$DATABASE_ID. A status of │ │ -H 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ 204 will be given. This indicates that the request │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ was processed successfully, but that no response │ │ │ │
│ │ body is needed. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ destroy_read_replica │ To destroy a specific read-only replica, send a │ DEL │ curl -X DELETE -H 'Content-Type: application/json' │ {} │
│ │ DELETE request to │ │ -H 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/replicas/$REPLICA_NAME. │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ Note: Read-only replicas are not supported for │ │ │ │
│ │ Redis clusters. A status of 204 will be given. This │ │ │ │
│ │ indicates that the request was processed │ │ │ │
│ │ successfully, but that no response body is needed. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ list_all_database_clusters │ To list all of the database clusters available on │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ your account, send a GET request to /v2/databases. │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ To limit the results to database clusters with a │ │ 'https://api.digitalocean.com/v2/databases' │ │
│ │ specific tag, include the tag_name query parameter │ │ │ │
│ │ set to the name of the tag. For example, │ │ │ │
│ │ /v2/databases?tag_name=$TAG_NAME. The result will │ │ │ │
│ │ be a JSON object with a databases key. This will be │ │ │ │
│ │ set to an array of database objects, each of which │ │ │ │
│ │ will contain the standard database attributes. The │ │ │ │
│ │ embedded connection and private_connection objects │ │ │ │
│ │ will contain the information needed to access the │ │ │ │
│ │ database cluster: The embedded maintenance_window │ │ │ │
│ │ object will contain information about any scheduled │ │ │ │
│ │ maintenance for the database cluster. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ list_all_databases │ To list all of the databases in a clusters, send a │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ GET request to /v2/databases/$DATABASE_ID/dbs. The │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ result will be a JSON object with a dbs key. This │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ will be set to an array of database objects, each │ │ │ │
│ │ of which will contain the standard database │ │ │ │
│ │ attributes. Note: Database management is not │ │ │ │
│ │ supported for Redis clusters. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ list_backups_for_a_database_cluster │ To list all of the available backups of a │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ PostgreSQL or MySQL database cluster, send a GET │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ request to /v2/databases/$DATABASE_ID/backups. │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ Note: Backups are not supported for Redis clusters. │ │ │ │
│ │ The result will be a JSON object with a backups │ │ │ │
│ │ key. This will be set to an array of backup │ │ │ │
│ │ objects, each of which will contain the size of the │ │ │ │
│ │ backup and the timestamp at which it was created. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ list_connection_pools │ To list all of the connection pools available to a │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ PostgreSQL database cluster, send a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/pools. The result will │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ be a JSON object with a pools key. This will be set │ │ │ │
│ │ to an array of connection pool objects. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ list_database_options │ To list all of the options available for the │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ offered database engines, send a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/options. The result will be a JSON │ │ 'https://api.digitalocean.com/v2/databases' │ │
│ │ object with an options key. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ list_database_users │ To list all of the users for your database cluster, │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ send a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/users. Note: User │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ management is not supported for Redis clusters. The │ │ │ │
│ │ result will be a JSON object with a users key. This │ │ │ │
│ │ will be set to an array of database user objects, │ │ │ │
│ │ each of which will contain the standard database │ │ │ │
│ │ user attributes. For MySQL clusters, additional │ │ │ │
│ │ options will be contained in the mysql_settings │ │ │ │
│ │ object. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ list_firewall_rules_for_a_database │ To list all of a database cluster's firewall rules │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ (known as trusted sources in the control panel), │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ send a GET request to │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ /v2/databases/$DATABASE_ID/firewall. The result │ │ │ │
│ │ will be a JSON object with a rules key. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ list_read_replicas │ To list all of the read-only replicas associated │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ with a database cluster, send a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/replicas. Note: │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ Read-only replicas are not supported for Redis │ │ │ │
│ │ clusters. The result will be a JSON object with a │ │ │ │
│ │ replicas key. This will be set to an array of │ │ │ │
│ │ database replica objects, each of which will │ │ │ │
│ │ contain the standard database replica attributes. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ migrate_a_database_cluster_to_a_new_region │ To migrate a database cluster to a new region, send │ PUT │ curl -X PUT -H 'Content-Type: application/json' -H │ {'region': 'lon1'} │
│ │ a PUT request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ │
│ │ /v2/databases/$DATABASE_ID/migrate. The body of the │ │ '$PAYLOAD' │ │
│ │ request must specify a region attribute. A │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ successful request will receive a 202 Accepted │ │ │ │
│ │ status code with no body in response. Querying the │ │ │ │
│ │ database cluster will show that its status │ │ │ │
│ │ attribute will now be set to migrating. This will │ │ │ │
│ │ transition back to online when the migration has │ │ │ │
│ │ completed. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ remove_database_user │ To remove a specific database user, send a DELETE │ DEL │ curl -X DELETE -H 'Content-Type: application/json' │ {} │
│ │ request to │ │ -H 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/users/$USERNAME. A │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ status of 204 will be given. This indicates that │ │ │ │
│ │ the request was processed successfully, but that no │ │ │ │
│ │ response body is needed. Note: User management is │ │ │ │
│ │ not supported for Redis clusters. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ reset_database_user │ To reset the password for a database user, send a │ POST │ curl -X POST -H 'Content-Type: application/json' -H │ {} │
│ │ POST request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/users/$USERNAME/reset_a… │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ For mysql databases, the authentication method can │ │ │ │
│ │ be specifying by including a key in the JSON body │ │ │ │
│ │ called mysql_settings with the auth_plugin value │ │ │ │
│ │ specified. The response will be a JSON object with │ │ │ │
│ │ a user key. This will be set to an object │ │ │ │
│ │ containing the standard database user attributes. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ resize_a_database_cluster │ To resize a database cluster, send a PUT request to │ PUT │ curl -X PUT -H 'Content-Type: application/json' -H │ {'size': 'db-s-4vcpu-8gb', 'num_nodes': 3} │
│ │ /v2/databases/$DATABASE_ID/resize. The body of the │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ │
│ │ request must specify both the size and num_nodes │ │ '$PAYLOAD' │ │
│ │ attributes. A successful request will receive a 202 │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ Accepted status code with no body in response. │ │ │ │
│ │ Querying the database cluster will show that its │ │ │ │
│ │ status attribute will now be set to resizing. This │ │ │ │
│ │ will transition back to online when the resize │ │ │ │
│ │ operation has completed. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ retrieve_an_existing_database_cluster │ To show information about an existing database │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ cluster, send a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID. The response will be a │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ JSON object with a database key. This will be set │ │ │ │
│ │ to an object containing the standard database │ │ │ │
│ │ cluster attributes. The embedded connection and │ │ │ │
│ │ private_connection objects will contain the │ │ │ │
│ │ information needed to access the database cluster. │ │ │ │
│ │ The embedded maintenance_window object will contain │ │ │ │
│ │ information about any scheduled maintenance for the │ │ │ │
│ │ database cluster. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ retrieve_connection_pool │ To show information about an existing connection │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ pool for a PostgreSQL database cluster, send a GET │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ request to │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ /v2/databases/$DATABASE_ID/pools/$POOL_NAME. The │ │ │ │
│ │ response will be a JSON object with a pool key. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ retrieve_database │ To show information about an existing database │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ cluster, send a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/dbs/$DB_NAME. Note: │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ Database management is not supported for Redis │ │ │ │
│ │ clusters. The response will be a JSON object with a │ │ │ │
│ │ db key. This will be set to an object containing │ │ │ │
│ │ the standard database attributes. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ retrieve_database_cluster_configuration │ Shows configuration parameters for an existing │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ database cluster by sending a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/config. The response is │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ a JSON object with a config key, which is set to an │ │ │ │
│ │ object containing any database configuration │ │ │ │
│ │ parameters. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ retrieve_database_user │ To show information about an existing database │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ user, send a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/users/$USERNAME. Note: │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ User management is not supported for Redis │ │ │ │
│ │ clusters. The response will be a JSON object with a │ │ │ │
│ │ user key. This will be set to an object containing │ │ │ │
│ │ the standard database user attributes. For MySQL │ │ │ │
│ │ clusters, additional options will be contained in │ │ │ │
│ │ the mysql_settings object. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ retrieve_read_replica │ To show information about an existing database │ │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ replica, send a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/replicas/$REPLICA_NAME. │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ Note: Read-only replicas are not supported for │ │ │ │
│ │ Redis clusters. The response will be a JSON object │ │ │ │
│ │ with a replica key. This will be set to an object │ │ │ │
│ │ containing the standard database replica │ │ │ │
│ │ attributes. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ retrieve_the_public_certificate │ To retrieve the public certificate used to secure │ GET │ curl -X GET -H 'Content-Type: application/json' -H │ {} │
│ │ the connection to the database cluster send a GET │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ request to /v2/databases/$DATABASE_ID/ca. The │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ response will be a JSON object with a ca key. This │ │ │ │
│ │ will be set to an object containing the base64 │ │ │ │
│ │ encoding of the public key certificate. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ retrieve_the_status_of_an_online_migration │ To retrieve the status of the most recent online │ GET │ curl -X PUT -H 'Content-Type: application/json' -H │ {'source': {}, 'disable_ssl': False} │
│ │ migration, send a GET request to │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ │
│ │ /v2/databases/$DATABASE_ID/online-migration. │ │ '$PAYLOAD' │ │
│ │ │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ │ │ │ │
│ start_an_online_migration │ To start an online migration, send a PUT request to │ PUT │ curl -X PUT -H 'Content-Type: application/json' -H │ {'source': {'host': │
│ │ /v2/databases/$DATABASE_ID/online-migration │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ 'source-do-user-6607903-0.b.db.ondigitalocean.com', │
│ │ endpoint. Migrating a cluster establishes a │ │ '$PAYLOAD' │ 'dbname': 'defaultdb', 'port': 25060, 'username': │
│ │ connection with an existing cluster and replicates │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ 'doadmin', 'password': 'paakjnfe10rsrsmf'}, │
│ │ its contents to the target cluster. Online │ │ │ 'disable_ssl': False} │
│ │ migration is only available for MySQL, PostgreSQL, │ │ │ │
│ │ and Redis clusters. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ stop_an_online_migration │ To stop an online migration, send a DELETE request │ DEL │ curl -X DELETE -H 'Content-Type: application/json' │ {} │
│ │ to │ │ -H 'Authorization: Bearer $DIGITALOCEAN_TOKEN' │ │
│ │ /v2/databases/$DATABASE_ID/online-migration/$MIGRA… │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ A status of 204 will be given. This indicates that │ │ │ │
│ │ the request was processed successfully, but that no │ │ │ │
│ │ response body is needed. │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ update_database_cluster_configuration │ To update the configuration for an existing │ PATCH │ curl -X PATCH -H 'Content-Type: application/json' │ {'config': {'autovacuum_naptime': 60, │
│ │ database cluster, send a PATCH request to │ │ -H 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ 'autovacuum_vacuum_threshold': 50, │
│ │ /v2/databases/$DATABASE_ID/config. │ │ '$PAYLOAD' │ 'autovacuum_analyze_threshold': 50, │
│ │ │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ 'autovacuum_vacuum_scale_factor': 0.2, │
│ │ │ │ │ 'autovacuum_analyze_scale_factor': 0.2, │
│ │ │ │ │ 'autovacuum_vacuum_cost_delay': 20, │
│ │ │ │ │ 'autovacuum_vacuum_cost_limit': -1, │
│ │ │ │ │ 'bgwriter_flush_after': 512, │
│ │ │ │ │ 'bgwriter_lru_maxpages': 100, │
│ │ │ │ │ 'bgwriter_lru_multiplier': 2, │
│ │ │ │ │ 'idle_in_transaction_session_timeout': 0, 'jit': │
│ │ │ │ │ True, 'log_autovacuum_min_duration': -1, │
│ │ │ │ │ 'log_min_duration_statement': -1, │
│ │ │ │ │ 'max_prepared_transactions': 0, │
│ │ │ │ │ 'max_parallel_workers': 8, │
│ │ │ │ │ 'max_parallel_workers_per_gather': 2, │
│ │ │ │ │ 'temp_file_limit': -1, 'wal_sender_timeout': 60000, │
│ │ │ │ │ 'pgbouncer': {'server_reset_query_always': False, │
│ │ │ │ │ 'min_pool_size': 0, 'server_idle_timeout': 0, │
│ │ │ │ │ 'autodb_pool_size': 0, 'autodb_max_db_connections': │
│ │ │ │ │ 0, 'autodb_idle_timeout': 0}, 'backup_hour': 21, │
│ │ │ │ │ 'backup_minute': 40, 'timescaledb': {}, │
│ │ │ │ │ 'stat_monitor_enable': False}} │
│ update_firewall_rules_for_a_database │ To update a database cluster's firewall rules │ PUT │ curl -X PUT -H 'Content-Type: application/json' -H │ {'rules': []} │
│ │ (known as trusted sources in the control panel), │ │ 'Authorization: Bearer $DIGITALOCEAN_TOKEN' -d │ │
│ │ send a PUT request to │ │ '$PAYLOAD' │ │
│ │ /v2/databases/$DATABASE_ID/firewall specifying │ │ 'https://api.digitalocean.com/v2/databases/$DATABA… │ │
│ │ which resources should be able to open connections │ │ │ │
│ │ to the database. You may limit connections to │ │ │ │
│ │ specific Droplets, Kubernetes clusters, or IP │ │ │ │
│ │ addresses. When a tag is provided, any Droplet or │ │ │ │
│ │ Kubernetes node with that tag applied to it will │ │ │ │
│ │ have access. The firewall is limited to 100 rules │ │ │ │
│ │ (or trusted sources). When possible, we recommend │ │ │ │
│ │ placing your databases into a VPC network to limit │ │ │ │
│ │ access to them instead of using a firewall. A │ │ │ │
│ │ successful │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
└─────────────────────────────────────────────────┴─────────────────────────────────────────────────────┴────────┴─────────────────────────────────────────────────────┴─────────────────────────────────────────────────────┘
And then you can run any of these commands in snake case (in the 'Route' column above); for example:
python3 cli.py --command api --route list_firewall_rules_for_a_database
Outputs pretty printed json:
{
"rules": [
{
"uuid": "41f1b3b4-a481-4ca7-8d92-f643a3606ac6",
"cluster_uuid": "e4f7e56e-2c29-4de1-b937-965bd2e20e9d",
"type": "ip_addr",
"value": "115.206.82.140",
"created_at": "2022-11-04T19:24:43Z"
}
]
}
You can also use doctl's database commands directly - with a nicely formatted output for tables and other types of data. Note that doctl is the python command line interface for digitalocean.
python3 cli.py --command doctl
You can also get doctl docs and api docs with this command:
python3 cli.py --command docs
You can get pricing of managed databases within digitalocean using public links:
python3 cli.py --command pricing
🐍 Python modules that may help you as you build PostgreSQL applications, as a beginner or an expert:
- asyncpg - a fast PostgreSQL Database Client Library for Python/asyncio
- click - click CLI docs
- cr8 - benchmarking postgres clusters
- flask-migrate - database migrations in flask
- marshmellow - schemas in python
- psycopg2 module - for querying databases in python code
- pgcli - command line interface for postgres
- pgloader - migrate to PostgreSQL in a single command
- python-digitalocean - a python command line client for digitalocean generally (mostly droplets)
- rich - a Python library for rich text and beautiful formatting in the terminal
- sqlalchemy - database toolkit for python
- tpch-pgsql - implementing the tpch benchmark for postgres databases
- unittest - unit testing in python
🐘 Standard postgres references that may help you:
- pgbench - pgbench can help you benchmark your database
- postgres - official postgres website
- postgres14 features - why use postgres14 over postgres13
🐳 DigitalOcean references that may help you as you use this CLI client:
- careers page - careers page (we're always hiring!)
- digitalocean database api docs - api docs with cuRL commands
- doctl docs - the official command line client for digitalocean
- hacktoberfest - DigitalOcean's annual hack-a-thon in October every year (open to anyone)
- managed databases page - managed databases product page
- postgres community page - community page for managed postgresql databases