Connectivity to the EXTERNAL client databases (Β§7) β the paying agency's own systems, reached over VPN.
Connectivity to the EXTERNAL client databases (Β§7) β the paying agency's own systems, reached over VPN. It is the only component that reads from or writes to anything outside CAPCON.
The Overview panel checks how much space is left on the client database server of the selected project (on the disk that holds the PostgreSQL data directory). At 3 % or less it shows the warning "Only N % (X GB) left. Clean up the database." The percentage is computed the same way as the Use% column of df -h: 3 % free means Use% 97 %.
Below it are listed the databases and the 15 largest tables of the project's database. They help decide what to clean up (for example old master backup copies).
The space is checked over the database connection (COPY ... FROM PROGRAM 'df'), so the database account must be a superuser. Otherwise only the database sizes are shown.
Use the menu on the left; each panel has its own help topic.
Status: not written yet. This topic is a stub created with the project template. The panel's owner (see
.github/CODEOWNERS) fills it in as the panel is built. Delete this quote block when you do.
TODO β one or two sentences: what question does this panel answer, and at what point in the CAP-control workflow does an operator open it?
The manifest describes it as: Configure and test connections to external client databases over VPN.
TODO β what must already be true. Most panels here need a project selected in the top bar, and the step before them to have completed.
TODO β name the outputs exactly: which table, which CSV, which folder under the project tree. An operator reading this later needs to find the artefact without reading the code.
TODO β the failure you actually hit while building this, and the check that distinguishes it from the next-most-likely cause. This section is the reason the help system exists; a list of buttons is not.
Status: not written yet. This topic is a stub created with the project template. The panel's owner (see
.github/CODEOWNERS) fills it in as the panel is built. Delete this quote block when you do.
TODO β one or two sentences: what question does this panel answer, and at what point in the CAP-control workflow does an operator open it?
The manifest describes it as: Retrieve updated vector and attribute layers from the client database.
TODO β what must already be true. Most panels here need a project selected in the top bar, and the step before them to have completed.
TODO β name the outputs exactly: which table, which CSV, which folder under the project tree. An operator reading this later needs to find the artefact without reading the code.
TODO β the failure you actually hit while building this, and the check that distinguishes it from the next-most-likely cause. This section is the reason the help system exists; a list of buttons is not.
It creates the year's declarations master table nma_<metai>.nma_adm_<metai>_master in the client database from NMA data, and later updates it with new packages. It is database/specs/create_master.sql turned into a form. Every Checks algorithm uses this table, so it is created at the start of the year and updated whenever a new package arrives.
client database set in the Client Connections panel (e.g. sagris_lit).
(sudo swanctl --initiate --child net240). The NMA data (pvsad.*) are oracle_fdw tables in the itree_nma database, and they do not answer without the tunnel.
client_db.user) must have anoracle_fdw user mapping for the server pvsad in the itree_nma database. If it does not, "Test connection to NMA" shows user mapping not found, and a database administrator has to create it.
itree_nma by default. Another one can be setin the project's client_db.source_dbname.
With "Manage the VPN automatically" ticked, the job brings the NMA VPN tunnel up on the x51 server (if it is down) and takes it down after a successful run (swanctl --terminate --ike itree). If the job fails, the tunnel is left up so that you can try again; take it down with the "Take down VPN" button. The "VPN status", "Bring up VPN" and "Take down VPN" buttons also let you manage the tunnel by hand.
The tunnel is managed through the database connection, not over SSH. x51 sits behind a router, and only the PostgreSQL port is forwarded from outside (54321 β 5432). The database account (sopr) is a superuser, so through COPY β¦ FROM PROGRAM it can run one program on x51 as the user postgres. CAPCON sends only one word (up, down or status), and the x51 script turns it into a swanctl command. If the account ever lost its superuser rights, tunnel management would stop working.
Setting it up on x51 (once, as root). The panel shows the exact contents:
/usr/local/bin/capcon-vpn: the script, owner root, chmod 755./etc/sudoers.d/capcon-vpn (edit with visudo -f). This file lets the userpostgres run only that script, and only with up, down or status.
sudo -u postgres sudo -n /usr/local/bin/capcon-vpn status.packages are shown (pak_id, pak_numeris, β¦).
how many fields have no ppz_danga.
after pressing "Edit code lists". Saved lists stay with the project (project.yaml, master_codes). A code may be in only one ppz_danga list and only one ppz_kodas list.
are loaded, or only the ones given. If a master already exists, it is not deleted: it is renamed to nma_adm_<metai>_master_iki<MMDD>, and a new one is created with the same columns. You confirm this with a tick.
pak_id).The run is a background job on the server: the panel only starts it and shows new log lines every 2 s. Closing the browser or reloading the page does not stop the job; when you open the panel again, it re-attaches to the running job. You can cancel it with the βΉ Stop button of the log window: the job stops within ~20 s, and the master is rolled back to its state before the run.
Packages are applied in ascending pak_id order:
glk_dkl_id from an older (or the same) package. If the master has no such record, they are inserted;
glk_dkl_id from the master. If a later package brings NAUor GEO again, the field is inserted again;
The summary shows them separately;
Then the whole master is recomputed: area_ha, perim_m, centr_x/y, ppz_danga from the lists (NULL for other codes), ppz_danga_reiksme = NULL and ppz_kodas from the lists (NULL for others).
| Where | What |
|---|---|
nma_<metai>.pvsad_new_data_<MMDD> | staging table for the NMA data unloaded that day. Dropped once the master is saved; it stays only if the master step fails (so that you can check what NMA returned). Nothing is created in the itree_nma database |
nma_<metai>.nma_adm_<metai>_master | the master table, with a unique index on glk_dkl_id and a spatial index |
nma_<metai>.nma_adm_<metai>_master_iki<MMDD> | the previous master, if a new one was created |
<project>/vectors/nma_adm_<metai>_prod_statistikai_<MMDD>.gpkg | for Nerijus, for computing statistics (glk_dkl_id, glk_paselio_kodas, geometry) |
<project>/vectors/lit_laukai_<metai>_<MMDD>.gpkg | send to Giedrius: LT parcels with geometries |
<project>/logs/master_<action>_<date_time>.json | the run record: parameters, code lists, summaries |
<MMDD> is the unload date. An existing GPKG is not overwritten: _v2, _v3β¦ is written instead (the layer name is the same as the file name). A second NMA table unloaded on the same day gets _v2.
user mapping not found for "<user>": the connection to itree_nmaworks, but the account has no oracle_fdw user mapping for the server pvsad. This is not a VPN problem.
not up.
unchanged.
ppz_danga: the summary lists the crop codes that are innone of the lists. Codes NTM, MVP, Ε½M-1β¦Ε½M-7, Ε PA and the like are left out of the lists on purpose, so this is not an error. Pay attention only to a new code you have not seen before.
(e.g. during a deploy) while the job was running. The master transaction was rolled back; run it again.
master stays as it was. During the run the master is locked for writing, so other jobs that change it wait.
In the itree_nma database, the schema pvprod consists of oracle_fdw foreign tables that point to the NMA Oracle schema "PVSAD" (server pvprod). This panel drops that schema and imports it again.
Do this ONLY when the table structure has changed on the Oracle side: a new table has appeared, a column has been added, renamed or changed, or a query returns ORA-00904: invalid identifier. When only the data has changed, no reload is needed: foreign tables read their data from Oracle on every query.
CAPCON itself reads pvsad.*, not pvprod.*, so this reload has no effect on CAPCON's own work.
The owner's commands:
DROP SCHEMA pvprod CASCADE;
VACUUM;
CREATE SCHEMA pvprod;
IMPORT FOREIGN SCHEMA "PVSAD" FROM SERVER pvprod INTO pvprod;
The panel runs them with three differences. All of them guard against losing anything:
DROP, CREATE and IMPORT run in one transaction. If IMPORT fails(VPN down, ORA-12170, no user mapping), pvprod stays as it was. Run one by one, the commands would leave no schema at all. VACUUM cannot run inside a transaction, so it is run after it. The result is the same.
DROP β¦ CASCADE removes themtogether with the tables. Previously, after a reload, ALTER TABLE β¦ OWNER TO sopr and similar commands had to be run by hand. Now they are read before the drop and restored, in the same transaction, for every table that comes back.
pvprodtables, CASCADE would drop them too. "Preview" shows them, and the reload is possible only after you tick that you agree to it.
database set (Client Connections).
server pvprod and the user mapping for the role exist, how many tables there are, who owns them, and what CASCADE would drop outside the schema.
IMPORT fetches the tabledefinitions from Oracle. If automatic VPN management is on in the NMA master table panel, the job brings the tunnel up itself and takes it down after a successful run. The tunnel is not taken down while an NMA master table job is running.
tables were imported, how many owners and privileges were restored, which tables are new and which are gone.
"is already being reloaded".
IMPORT has 180 s, and the whole job about 270 s. If VACUUM does notfinish in the remaining time, it is cut off. The schema has already been reloaded by then, so this does no harm, and VACUUM can be run by hand.
pvprod schema that are not foreign tables (e.g. viewscreated by hand) are not restored by IMPORT. "Preview" shows them.
client.push permission can run a reload.Status: not written yet. This topic is a stub created with the project template. The panel's owner (see
.github/CODEOWNERS) fills it in as the panel is built. Delete this quote block when you do.
TODO β one or two sentences: what question does this panel answer, and at what point in the CAP-control workflow does an operator open it?
The manifest describes it as: Assemble current results into delivery packages.
TODO β what must already be true. Most panels here need a project selected in the top bar, and the step before them to have completed.
TODO β name the outputs exactly: which table, which CSV, which folder under the project tree. An operator reading this later needs to find the artefact without reading the code.
TODO β the failure you actually hit while building this, and the check that distinguishes it from the next-most-likely cause. This section is the reason the help system exists; a list of buttons is not.
Not yet tried against the real databases. Sending a package to NMA is disabled for now: the checks can be run, but the package is not sent to
pvsad.pvsad_poz.
algorithm (Checks) left in the table nma_<metai>.pvsad_pak_pozymiai, and deliver it to the NMA table pvsad.pvsad_poz.
algorithms produced for the selected period.
list).
algorithm clears the pvsad_pak_pozymiai table and fills it again, so it always holds only the latest run's package.
taken down after the NMA queries (as in the NMA master table panel; this needs the capcon-vpn script on the x51 server).
The previous package of this algorithm is found in NMA and added automatically.
ppz_pak_id), the data date(ppz_pagrindo_data) and the delivery date (ppz_skaiciavimo_data) are shown, followed by the table of check results.
active only when there is not a single error (β).
| Test | What is checked |
|---|---|
| Columns and types | the source has every column the INSERT reads, pvsad.pvsad_poz has every column it writes, and the types are compatible |
| NULL / empty cells | a column is filled in every row, or not used by this algorithm. A partly empty column is an error unless it is a known exception (e.g. DGP < 0.1 ha). Exceptions are shown with "!" and must be named in the e-mail to NMA |
| Number of declared fields | the row count equals the number of master table fields the algorithm covers. No duplicate, missing or extra fields |
| Number of crop groups | the package contains every crop of those master fields (glk_paselio_kodas, DISTINCT) |
| IDs not double | ppz_id, ppz_dkl_id, ppz_pak_id, ppz_pak_pak_id are not double, neither in the source nor in NMA |
| Crop classification | ppz_kodas / ppz_danga hold only the algorithm's values (e.g. only PSL, not PDJ) |
| Probabilities valid | probabilities are between 0 and 1, present where and only where there is a crop, q<n>_proba + q<n>_proba2 β€ 1 |
| Staging table | itree_nma has public.pvsad_pak_pozymiai with every column the INSERT reads |
| Comparison with packages | which columns are filled in this package and in the NMA packages. Differences are shown with "!" |
The number of fields is checked against the master table, not against the "declared fields" spreadsheet sheet.
The checks write nothing. The delivery (once enabled) works like checks/specs/PDJ/PDJ_0630.sql:
itree_nma database it clears the staging tablepublic.pvsad_pak_pozymiai (TRUNCATE) and copies the package into it from sagris_lit nma_<metai>.pvsad_pak_pozymiai. Columns are copied by name. It checks that the row count is the same and the package is the same, and the table is kept. It can be inspected after the delivery. Note: between deliveries it holds the last delivered (or attempted) package, not necessarily the one now in sagris_lit;
pvsad.pvsad_poz, it runs the INSERT intopvsad.pvsad_poz from the staging table. The query can be seen in the panel ("INSERT query"). Before the commit it checks that the package is visible and is the latest one. If not, the INSERT is rolled back and nothing is sent to NMA;
nma_<metai>.pakeliai_<first>_<last> and renames it after the package just delivered. The first time in a year the table is created from the first delivered package, e.g. nma_2026.pakeliai_334_334 β after package 335 nma_2026.pakeliai_334_335, and so on.
The delivered-packages table is updated only when the package has been delivered to the iTree database and that check has passed. If the same package is already in the table, the check "Package not yet delivered" shows an error and the delivery is refused. The schema must hold only one pakeliai_*_* table.
nma_<year>.pvsad_pak_pozymiai does not exist (an error from theclient database): no Checks algorithm has been run for this year yet.
algorithm's package. Choose that algorithm, or run the one you want again.
Database β NMA master table, the VPN section.
bar.