When you are storing important data in a PostgreSQL database like information about your customers, products, sales, employees etc. you need to make sure you have a full backup of that database. Performing regular database backups very important as it will save you lot of time in case of data loss. When you install PostgreSQL on a Linux VPS, it includes tool which helps you to better manage the database service. There is also a tool for generating database backups and it is really easy to use. Follow the steps bellow to find out.

How to backup a PostgreSQL database on a Linux VPS

One of the prerequisites is to have SSH access to your server. If you do have access to it, connect to your Linux VPS via SSH. Once you are connected to the server, switch to the postgres user using the following command:

su - postgres

postgres is the superuser in PostgreSQL and you can use it to perform many different operations like adding users, creating or deleting databases, assigning roles etc. In this case you will be using the superuser to generate a backup of your PostgreSQL database.

To backup a single database you can use the pg_dump tool which is installed by default with your PostgreSQL service. You can use it no matter if you have an Ubuntu VPS or CentOS VPS. When you are using this tool to generate a backup of specific database, you should use the following syntax:

pg_dump [connection-option ...] [option ...] [dbname]

Now, to create a plaintext dump of the database, you can use the command bellow:

pg_dump database-name > database-name.sql

If you are not sure about the name of the database you are using for your application, you can easily check this with the psql tool. This tool is also included with the PostgreSQL service during installation. Therefore, while logged in as postgres user, type psql in your terminal to activate the PostgreSQL interactive terminal:

postgres@host:~$ psql
psql (9.5.13)
Type "help" for help.

postgres=#

Here, type \l to list all of your PostgreSQL databases. The output will be similar to the one below, except there will be more databases:

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

Now, lets say you want to backup the testdb. The command you need to use is the following:

pg_dump testdb > testdb.sql

How to backup all your PostgreSQL databases on a Linux VPS

The pg_dumpall tool that also comes with PostgreSQL allows you to backup multiple databases at once. You can use it to create a full backup of all your PostgreSQL database. To do that, run the command below in your terminal:

pg_dumpall > alldbs.sql

This will create a plaintext dump of all your databases currently stored in PostgreSQL so you can restore them later if needed.

How to restore PostgreSQL database on a Linux VPS

The psql tool also allows you to easily restore a database from backup if needed. To restore a database from backup, you can simply run the following command in your terminal:

psql database-name < database-name.sql

Of course, you need to replace database-name in the previous command with the name of your database. The restore operation will take some time depending on the size of the backup.

You can also restore all database from a previous backup using the following command:

psql -f alldbs.sql postgres

Make sure you replace alldbs.sql with the name of the database backup file.

For more information and usage examples about these PostgreSQL tools we recommend you to check their respective documentation pages. The documentation for pg_dump is available here, for psql here, and for pg_dumpall it is available here.

And that’s it, you have successfully learned to back and restore PostreSQL databases. We strongly suggest you do a regular daily backups of your databases and keep them in a safe place.

You really don’t need to back up or restore PostreSQL database if you use one of our premium server management services, in which case our system administrators will take care of your database backups, professionally and efficiently.

The post How to Back Up Your PostgreSQL Database appeared first on LinuxHostSupport.

Check the size of all MySQL databases

To check the size of MySQL databases and tables we can use the virtual database called information_schema which provides access to database metadata, including the size of the databases and tables.

Enter the MySQL shell as user root by executing the following command:

mysql -u root -p

To print the size of all your databases run the following command:

SELECT 
    table_schema "Database Name",
    ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS "Database Size in MB"
FROM 
    information_schema.TABLES
GROUP BY table_schema ; 

The output should be similar to the following:

+-----------------------+---------------------+
| Database Name         | Database Size in MB |
+-----------------------+---------------------+
| admin_110             |              251.28 |
| admin_120             |               18.99 |
| admin_25              |               11.86 |
| admin_562             |               28.45 |
| admin_877             |                4.20 |
| information_schema    |                0.14 |
| mysql                 |                0.69 |
| performance_schema    |                0.00 |
| user_174              |               23.72 |
| user_379              |                0.26 |
| user_604              |                0.64 |
| user_625              |                1.22 |
| user_820              |                0.73 |
| user_854              |                2.25 |
| user_901              |                1.10 |
| user_961              |                1.60 |
+-----------------------+---------------------+

Check the size of all Tables in a specific MySQL database

To print the size of all tables in a specific MySQL database, same as before we will use the information from the virtual database information_schema:

SELECT 
    table_name as "Table Name",
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size in MB"
FROM 
    information_schema.TABLES 
where 
    table_schema = 'admin_562';

The output should be similar to the following:

+-----------------------+------------------+
| Table Name            | Table Size in MB |
+-----------------------+------------------+
| wp_commentmeta        |             0.05 |
| wp_comments           |            12.42 |
| wp_links              |             0.03 |
| wp_options            |             1.98 |
| wp_postmeta           |             0.19 |
| wp_posts              |             1.75 |
| wp_term_relationships |             0.06 |
| wp_term_taxonomy      |             0.05 |
| wp_termmeta           |             0.05 |
| wp_terms              |             0.05 |
| wp_usermeta           |             0.05 |
| wp_users              |             0.06 |
| wp_wfBadLeechers      |             0.02 |
| wp_wfBlockedIPLog     |             0.06 |
| wp_wfBlocks           |             0.03 |
| wp_wfBlocksAdv        |             0.02 |
| wp_wfConfig           |             3.28 |
| wp_wfCrawlers         |             0.02 |
| wp_wfFileMods         |             1.52 |
| wp_wfHits             |             2.20 |
| wp_wfHoover           |             0.03 |
| wp_wfIssues           |             2.52 |
| wp_wfKnownFileList    |             0.20 |
| wp_wfLeechers         |             0.05 |
| wp_wfLockedOut        |             0.02 |
| wp_wfLocs             |             0.02 |
| wp_wfLogins           |             0.08 |
| wp_wfNet404s          |             0.03 |
| wp_wfNotifications    |             0.02 |
| wp_wfPendingIssues    |             0.31 |
| wp_wfReverseCache     |             0.02 |
| wp_wfSNIPCache        |             0.06 |
| wp_wfScanners         |             0.02 |
| wp_wfStatus           |             1.17 |
| wp_wfThrottleLog      |             0.03 |
| wp_wfVulnScanners     |             0.02 |
+-----------------------+------------------+

Check the size of a specific Table in a specific MySQL database

In the last example we will show you how to check the of a specific table size by the database name and the table name, also by querying the virtual database information_schema :

SELECT 
    table_name as "Table Name",
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size in MB"
FROM 
    information_schema.TABLES 
where 
   table_schema = 'admin_877' and table_name = 'wp_wfConfig';

The output should be similar to the following:

+-------------+------------------+
| Table Name  | Table Size in MB |
+-------------+------------------+
| wp_wfConfig |             3.28 |
+-------------+------------------+

If you use one of our Outsourced Linux Server Support Services, you can simply ask our expert Linux admins to check the size of your MySQL databases and tables for

you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post, on how to check the size of MySQL databases and tables, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

The post How to Check MySQL Database Size appeared first on LinuxHostSupport.