# Databases: PostgreSQL, more than one per website, and the work around them > Source: https://www.allsweb.com/sixpanel/docs/databases > Markdown for agents: https://www.allsweb.com/sixpanel/docs/databases.md > Publisher: AllsWeb (www.allsweb.com) Part of: SixPanel documentation **What this page is for:** give a website a **PostgreSQL** database beside MariaDB, give one website **more than one database**, and do a database's everyday work on SixPanel's own pages — users and what each may do, the tables and their upkeep, the character set, export, import and delete. phpMyAdmin is still there for looking inside a database; the work that changes who may reach what is done here, the way cPanel does it on its own Database page. **You need** - The owner's login for anything that changes a database. A temporary login can look at every page on this subject and change nothing; the read-only demo shows them too. - For PostgreSQL: a server on **Ubuntu 26.04, Ubuntu 24.04 or Debian 13** with at least **1 GB of memory**. It comes from your system's own packages — PostgreSQL 18 on Ubuntu 26.04, 16 on Ubuntu 24.04, 17 on Debian 13 — and is kept up to date by its security updates. **What does not change:** a website's first MariaDB database stays exactly where it was, with the same name, the same user, the same password and the same variables. Nothing is moved or rewritten when you update SixPanel. **A 6amMart shop keeps its one database** — it is made for the shop and fixed — so it gets the safe tools (below) and nothing that adds or removes a database. --- ## PostgreSQL ### Install it Open **Server**, the **Software** tab, and press **Install** on the **PostgreSQL** card. The card says first what it needs (memory, disk, the systems it runs on) and, on a server where it cannot run, why. The install is a job you can follow from the top bar; it takes a minute or two. What SixPanel sets up, and keeps that way: - **It answers on this server only** — `127.0.0.1` and its socket, never a public address. The access rules (`pg_hba.conf`) start with a block SixPanel writes: the server's own `root` reaches the administrator over the socket, every database user signs in with a password, and **any other address is refused**. The block is written again at every panel start. - **Passwords are `scram-sha-256`**, and the panel hands PostgreSQL only the stored form of each one, never the password itself. - **Sized for this server**: its memory buffers, connections and per-query memory follow the server's memory and cores, the way MariaDB's are, and it has a memory ceiling of its own so a busy database cannot take the whole server. It is started again by itself if it ever stops. - **MariaDB and PostgreSQL share one memory budget.** The memory a server sets aside for its databases is the same with one database or two, so the PHP workers, Redis, your apps and Docker keep what they had. With PostgreSQL on the server, that budget is shared by **how much data each one holds**. When both fit, each keeps room for all of its data and the rest is shared in the same proportion; when they do not, the one with more data gets more. PostgreSQL keeps half of its share as its own buffers and leaves the other half to the system's file cache, which it reads through as well. The panel weighs both databases when it starts, every six hours, and at every update. MariaDB moves to its new share at the next update, or at once with **Apply** on the server's tuning. A value you set yourself on **Server → Database** is never changed. PostgreSQL also gets ten connections for each website that keeps a database in it. - **Nobody but a database's own users may open it.** PostgreSQL normally lets every account connect to every database; SixPanel takes that away for each database it makes. If PostgreSQL was already on the server before SixPanel, the panel looks after the same settings, and removing it from **Software** later leaves its packages in place. ### Look after it **Manage** on its Software card (or **Server → Database**, where it has a card beside MariaDB) shows whether it is running, where it answers, how it was sized, how many connections are in use, settings that wait for a restart, and every database in it with the website that keeps it. **Restart** writes SixPanel's settings again and restarts it — the websites that use it lose their connections for a few seconds. **Health** has a **PostgreSQL** row: red when a website keeps a database there and PostgreSQL is stopped or gone, or when it answers on a public address; amber when its access rules no longer start with SixPanel's block, or when settings wait for a restart. ### Remove it **Remove** on its Software card. It is refused while a website still keeps a database there — delete those on each website's **Database** page first. Its data stays on the disk unless you choose to delete it in the same dialog, so installing it again finds every database as it was. --- ## More than one database for a website Open the website, then **Database**. The **Databases** card lists every database the website has — the first one marked **first** — with its engine, size and number of users. A website is **made** with MariaDB or with no database. For a first database of another engine — PostgreSQL, MongoDB or SQL Server — make the website with **No database**, then add it here: **Add a database** makes it the website's first, under the first database's name. **Add a database** asks two things: - **Engine** — the ones this server has. One that is not installed says so, with the way to **Software**; one that cannot run here says why. - **Name** — a short name, lowercase letters and digits. The database and its own user are called `site__`, at most 32 characters (the dialog says how many are left). A website's **first** database needs no name: it is `site_`, whatever its engine. One name is one thing on the whole server: a name another website or a shop already uses, or a database or account the engine already has that no website here owns, is refused — somebody else's data never becomes this website's because a name matched. Node.js apps, PHP websites, WordPress and Create with AI websites can have up to eleven databases. A static website has none; a staging site has the databases of the website it is a copy of. ### How the app reaches them - **The first database** gives the app today's variables: `DATABASE_URL`, `DB_CONNECTION`, `DB_HOST`, `DB_PORT`, `DB_DATABASE`, `DB_USERNAME`, `DB_PASSWORD`. A Node.js app gets them in its environment by itself (restart it after a change); a PHP website writes them into its `.env` with **Write these into the app's .env**. That is true when the first database is PostgreSQL too — `DB_CONNECTION` is then `pgsql`, which is what Laravel reads. - **Every other database** has names of its own, made from its name: a database called `site_shop_reports` is `REPORTS_DATABASE_URL`, `REPORTS_DB_HOST` and so on. **They are never put into the app by themselves** — open the database (**Manage**) and press **Add to the app's environment** (Node.js) or **Write into the app's .env** (PHP). - A PHP website given a PostgreSQL database also gets PHP's `pgsql` extension for its PHP version, when that version does not have it yet. PostgreSQL is reached over `127.0.0.1` on its port (5432 unless the server set another); MariaDB over its socket (`localhost`) from PHP and `127.0.0.1` from Node.js. --- ## A database's tools **Manage** on a database's row opens its tools under the list. **Connection** — its host, port, name and user, its address without the password, and the variables the app reads it by. **Show password** shows its password; only the owner can, and not many times a minute. (The first MariaDB database's password, and **Change password**, stay on its own card above, because changing it also changes `wp-config.php` and the `.env`.) ### Users Every database has its own user, which the app signs in with. **Add user** gives it another, with a password the panel makes and keeps: - **Read-only** — on MariaDB, `SELECT` and `SHOW VIEW` on that one database; on PostgreSQL, connecting and reading every table there is now and every table made later. For a reporting tool, a dashboard or a person who should look and not touch. - **All privileges** — everything the database's own user may do there, and nothing outside it. **Make read-only** / **Give all privileges** changes one, **Show password** and **New password** work as for the database itself, and **Remove** deletes the user — the database and its data stay. A new password for the database's own user follows into the app's settings where the panel wrote them (its environment, or a `.env` that names this database); anything else using the old one needs the new one. ### Tables Every table with its rows and size (the number of rows is an estimate for InnoDB tables). Tick tables — or none, for every table — and choose: | | MariaDB | PostgreSQL | |---|---|---| | **Check** | reads each table through and reports what it finds; changes nothing | — | | **Repair** | mends a MyISAM or Aria table; InnoDB tables look after themselves and are skipped | — | | **Optimize** | rebuilds each table to give back the space deleted rows left — a large table takes a while and the app waits on it, so pick a quiet moment | — | | **Analyze** | refreshes the statistics the database plans its queries with | the same | | **Vacuum** | — | makes the space dead rows hold reusable and refreshes the statistics; the tables stay in use | | **Reindex** | — | rebuilds each index beside the old one, without locking the table | Each is a job, a table at a time, with every table's result in its log. A full rewrite under a lock (`VACUUM FULL`) is never offered. ### Character set MariaDB only. The database's character set and collation now, and **Change**: tables made from then on use it. **Also convert every table** rewrites each table in the new one — export the database first, and give a large database time. A PostgreSQL database here is UTF-8, which holds every language, and is not changed afterwards. ### Export and import **Export now** writes a file of the whole database onto the server — `.sql.gz` for MariaDB, a `.dump` (PostgreSQL's own compressed format) for PostgreSQL — and keeps the newest five, with a **Download** each. **Import a file** replaces what is in the database. Before it runs, the database as it is now is kept on the server (the newest two such copies stay), and put back if the import stops; with no room on the disk for that copy the import is refused and nothing changes. The file is read **as the database's own user**, so it can change that database and nothing else. MariaDB takes `.sql` and `.sql.gz`; PostgreSQL also takes a `.dump` made by `pg_dump -Fc`. (The first MariaDB database keeps its own **Export & import** card.) ### Delete this database Type the database's name to confirm. The database, its users and its exports are deleted, and the panel cannot bring it back by itself — export it first if you may need it. Deleting the **first** database takes `DATABASE_URL` and `DB_*` away from the app. WordPress's own database is never deleted on its own: delete the website instead. --- ## A 6amMart shop's database The shop's **Database** page has a **Tables** section (open it to load it): every table with its rows and size, the character set the shop was installed with, and **Check**, **Analyze**, **Optimize** and **Repair** for the tables you tick. **Database care**, above it, already does the regular clean-up by itself; these are for when you want one now. Nothing on that page makes or drops a database, adds a user or changes the character set: the shop has one database, made for it, and its own updates expect it as it is. --- ## phpMyAdmin, kept small phpMyAdmin is for looking inside a database and running a query. What changes who may reach what is on SixPanel's pages, so phpMyAdmin no longer offers it: - no **Create database** and no **Drop database** (a typed `DROP DATABASE` is refused too), no **Change password** — a database's password is changed on its card, which also changes the app's settings — and no server information; - the server's own system databases are not listed; - **Open in phpMyAdmin** on a Database page signs in as **that database's own user** and shows **that one database** and nothing else; - nobody signs in as `root`, and a database's user has no right to make users. Signing in at phpMyAdmin's own login form (for another tool's user, say) still works as before. --- ## The Database manager: the data inside, for every engine phpMyAdmin is MariaDB's. The **Database manager** does the same work inside the panel for every engine a website's database can be in — MariaDB, PostgreSQL, SQL Server and MongoDB — and for a 6amMart shop's database. **Open it** with **Manage data** on the **Database manager** card at the end of a website's (or a project's) Database page. It opens on that database, inside the same page, with five tabs: - **Tables** (MongoDB: **Collections**) — each one with its rows and size. **Browse** opens it. - **Rows** (MongoDB: **Documents**) — a page at a time (25, 50, 100 or 250), with a **Search** in every column or in one, and a press on a column's name to sort by it. **Add a row**, **Edit** and **Delete** work on a table with a primary key: a row is named by its key, so a table without one is changed with a query. A value can be set empty (**NULL**), binary data is written in hexadecimal, and a value too long to show in full is changed with a query. In MongoDB a document is edited whole, in Extended JSON (`{"$oid": …}`, `{"$date": …}`), so its types stay what they were; the filter and the sort are Extended JSON too. - **Structure** — a table's columns (type, empty or not, default), its primary key and indexes, and a view's definition. MongoDB has no fixed columns: the fields its documents use are read from a sample of them. - **Query** — any statement (MongoDB: a find with a filter, or an aggregation pipeline). At most 1,000 rows of each result are shown, and it is stopped after 30 seconds, 2 or 5 minutes (you choose). **Read only** runs one statement that reads, in a transaction that is thrown away, so nothing it does is kept. A statement that changes something asks first. - **Import & export** — one table as **CSV**, **JSON** or **SQL** (MongoDB: one document a line) to download, at most 500,000 rows. An import adds rows to the chosen table from CSV (a header line of column names) or JSON, in one go: a row the database refuses stops it and nothing is kept. An **SQL** file runs as a script in the database. At most 64 MB and 50,000 rows — a whole database is still exported and imported on its card. **It signs in as the database's own user** — the one the website's app uses — never as `root`, `postgres`, `sa` or MongoDB's root user. So it can reach that database and nothing else, and what it may do there is what the app may do. No password is shown on any of these pages. **Who may use it:** you, everything. A reseller or user account: everything, on its own websites' databases. A **temporary login** may look — the tables, the rows, the structure — and its queries are always read-only; it cannot change, export or import. The **demo** sees the tables only, never a row. **Tia** sees the tables and their structure, and never a row, a query, an export or an import. The engines are reached on this server only — MariaDB and PostgreSQL over their sockets, MongoDB and SQL Server on 127.0.0.1 — so nothing new is opened to the internet. --- ## Backups, restores, staging sites and moves - **Backups** take every database of every website — each in its own engine's format. A database that could not be dumped is named in the backup's result and left out of that one backup; the rest goes on. - **Restoring a website's database** puts back each of its databases from the backup, each as its own user, and keeps each as it is first; one that cannot be imported is put back as it was. - A **staging site** is made with the website's first database only. Its files are searched for the passwords of production's other databases too, and a staging site that still holds one is not started until that file is changed (its card says which). - **Moving a website to another server** carries its first database only, for now. A website with other databases or extra users is refused before anything is copied: export them, remove them, move the website, then add and import them on the other server. --- ## Tia Tia can list a website's databases, their users, tables and sizes, and — asking you first — add or delete a database, add a user or change its access, run the table upkeep, change the character set and export. **She never shows a password, downloads an export or imports a file**: those are yours, on the website's Database page. --- ## How to check it worked - **Software → PostgreSQL** reads **running**, on `127.0.0.1` only, and **Health** has a green **PostgreSQL** row. - The website's **Databases** card lists the new database with its engine and a size. - On the server: `sudo -u postgres psql -c '\l'` lists it, owned by its own user; `sudo ss -ltnp | grep postgres` shows `127.0.0.1:5432` and nothing on a public address. - From the website's account, its user connects: for PostgreSQL, `psql -h 127.0.0.1 -p 5432 -U site_shop_reports site_shop_reports -c 'select 1'` (the port, user and database as **Connection** shows them) asks for the password — paste it from **Show password**. Never put the password on the command line, nor a connection URL that holds it: a program's command line is in the process list, which every account on the server reads. ## If it went wrong - **"PostgreSQL is not installed on this server"** when adding a database — install it on **Software** first; the dialog links there. - **A name is refused as already used** — another website, a shop or the engine itself has it. Choose another name; **Server → Database** lists what is there. - **The PHP app says it cannot find a driver** — the website's PHP version lacks `pdo_pgsql`. Install the `pgsql` extension for that version on **Software → PHP**, or add the database again (its job installs it when it is missing). - **A move is refused because of other databases** — export them on the Database page, remove them, move, then add and import them on the other server. - **PostgreSQL's Health row says its access rules changed** — press **Restart** on its Software view (or restart the panel): SixPanel writes its block at the top again. ## The software card on this page A project's or website's **Database** page shows the cards of the database engines (and the Redis) it uses. It is the same card as on **Software**: its state, its version, a waiting update and, for the owner, Restart (asked first, and refused while another job runs). **On Software** opens its card there, with who else uses it. See [Software, search and the system monitor](https://www.allsweb.com/sixpanel/docs/software-search-monitor).