1.1   What is PostgreSQL?

The PostgreSQL database is one of the leading open-source relational databases available. A full range of documentation is available at https://www.postgresql.org/docs.

1.2   What is the ACNET Database?

The ACNET database consists of a single PostgreSQL instance, also known as a "cluster", that contains these databases:

• adbs contains device settings, parameters, and application data from many ACNET console applications.
• bdbs contains device save/restore information and historical data from the Tevatron era of operations.

Previously, there were three databases: accdb, which contained the accelerator database, which is maintained via dabbel. This was the first database. Later, appdb was created which was the database used by individual programs to store program-specific data. After that apvdb was created, which was a supplement to appdb.

The current adbs database is now, essentially, appdb, with apvdb merged in (apvdb no longer exists). The contents of accdb were merged into appdb, and all of the objects in accdb placed into a _schema_ called accdb. The database "accdb" no longer exists either.

The current adbs is a amalgamation of three previous historical databases.

1.3   What environments are there?

Three different database environments exist:

• Production The operational database resides on hosts dbsrv1 and dbsrv2, and is accessible as host name dbsrv.fnal.gov. These two hosts operate as primary and hot-standby pair, and the role of primary can be assumbed by either server. The only access to the production database is via dbsrv.fnal.gov.
• Test Active development and testing is supported on host dbsrv3. This server contains a copy of the production database, and is refreshed somewhat infrequently. Users of the test database are cautioned to maintain copies or scripts of any data they place into the test database, as when it is refreshed with a recent production copy, all customizations are lost.
• Integration This is a point-in-time copy of the production database. It is currently used primarily to reference the state of the database when it was first converted from Sybase in 3Q2022.

1.4   I am developing an application that needs a database, can I use the ACNET database?

If you are developing an ACNET console application, the answer is probably yes. If your application is for some other operational need,the answer is maybe.

A few considerations all application owners that utilize the database should think about and have a good estimate of before requesting access:

• How much storage space will my mature application require? Generally, individual applications should require 2GB or less of DB table space.
• How many transactions/second will my application utilize? A good estimate of this metric, along with the type of transaction, i.e. read, insert, update, is essential to guessing the impact on the database server.
• How many connections to the database will my application require? The number of connections to the database is a finite resource, and the connection pool is shared by all applications. Every application should strive to keep a DB connection open only long enough to efficiently conduct its transactions. Persistent connections to the database are strongly discouraged! Ideally, each application should utilize one connection to the database.
• Does my application require any customizations to the server configuration? If the answer to this question is yes, you probably need your own dedicated database instance. The ACNET database servers are configured specifically for console application usage.

1.5   Why would I need a DB account?

You may need a database account if:

• You are an application developer that needs a relational database storage backend, and the application will be used primarily by control room operations personnel.
• You are taking over maintenance responsibility for an application that already uses the ACNET database.

1.6   How do I get a DB account?

Access to the ACNET database requires a "Linux" Kerberos account, i.e. one like "user@FNAL.GOV". If you do not have a Kerberos account yet, please request one via the service desk.

Once you have a Kerberos account in the FNAL.GOV realm, send an email to postgres-dba@outpost.fnal.gov with the following information:

• Your Kerberos principal name.
• The requested schema name. This name should be the same name as your application, or a more general reference to the type of application(s) it will contain.

In the past, users were issued a schema named identical to their user name. This practice has been discontinued as it leads to long-term maintenance confusion.

Once a database account has been created you will have the following environment:

• User role/name Each database user has their own unique user name, which is the same as a role in PostgreSQL databases. This user name will be the same as the user's Kerberos principal name. User roles are granted privileges from a schema role in order to work within an application's schema.
• Schema This is the logical "space" within the database that will contain your tables, functions, and other objects associated with your applications. This schema will be owned by a role with the same name as the schema. For example if your schema is named "awesomeADapp", there will be a role named "awesomeADapp" which will own the schema and have full control of all objects within it.
• Schema role This role should be the primary role used to manage objects within your application's schema. This is most easily done by setting your current session role to the schema role using the SET ROLE 'schema_rolename' SQL command.

1.7   How do I log into the DB?

A valid Kerberos ticket that matches your database role name is required. Application software accessing the database must use the password assigned to the application by the database admin team.

The PostgreSQL client application psql is the most commonly used method to access the database.

• Ensure you have a valid Kerberos ticket.
• Ensure you are connecting from a host within the controls network. Kerberos authentication to the database will not work correctly via a proxy or through port forwarding.
• Use psql to connect to the database. As long as your Kerberos ticket is valid, there is no need to specify the user name, just the host (-h) and database (-d) names.

clx80$ psql  -h dbsrv3 -d adbs
psql (17.0, server 14.12)
GSSAPI-encrypted connection
Type "help" for help.

adbs=>

1.8   Where can I access the DB from?

Any host within the controls network can access the database. Hosts on the general network, i.e. most users workstations, can not directly access the database. The most commonly used hosts to access the database are the "Console Linux" or "clx" nodes. The psql postgres client application is present on the clx hosts.

1.9   What can I access in the DB?

Any object within your assigned application's schema should be fully manageable by the application schema role. Objects from other schemas may also be readable/selectable via group membership roles.

Contact a database administrator at postgres-dba@outpost.fnal.gov if your application requires access to another schema's objects and is unable to do so.

1.10   I've made a terrible mistake. How do I recover?

If you mistakenly damage data or objects within your application schema, it is possible for database administrators to restore them from backup. Daily and weekly backups from the last several days and/or weeks are readily available to restore from. It is also possible to go back several months or years by accessing tape archived backups.

Restoring objects from backup is not a routine task, and the database administrators dislike it greatly. Please only ask for data restoration if there is no other way to recover your data. Send an email to postgres-dba@outpost.fnal.gov to request data restoration.

When requesting a restoration, please exactly identify the tables or other objects to be restored, and the date (preferably within the last few days) to restore from.

Advice for recovering from mistakes not associated with the database should be directed to the deity of your choosing.

1.11   Why don't I see any objects in the database?

All objects in the database are stored in dedicated schemas and are not displayed by the psql client's default "\d" command, which without options only list objects in the "public" schema. That is to say, nothing in the database is stored in the default "public" schema.