{"id":30202,"date":"2024-07-17T18:03:02","date_gmt":"2024-07-17T12:33:02","guid":{"rendered":"https:\/\/mobisoftinfotech.com\/resources\/?p=30202"},"modified":"2025-12-30T17:24:42","modified_gmt":"2025-12-30T11:54:42","slug":"master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial","status":"publish","type":"post","link":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial","title":{"rendered":"Using PgSQL Command Line Utilities: psql, pg_dump, and pg_restore Tutorial"},"content":{"rendered":"<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p>PostgreSQL, also known as Postgres, is a powerful, open-source relational database management system that emphasizes extensibility and SQL compliance. For developers and database administrators, efficiently managing PostgreSQL from the command line is a crucial skill. This tutorial will guide you through common PostgreSQL command line utilities using the command line on Ubuntu 22.04, leveraging Docker to facilitate running multiple PostgreSQL versions simultaneously. By the end of this tutorial, you will be comfortable with starting a PostgreSQL instance, connecting to it, performing basic database operations, and handling backups and restores.<\/p>\n\n\n\n<p>If you prefer video version of this tutorial, it\u2019s available here:<\/p>\n\n\n\n<iframe width=\"560\" height=\"315\" src=\"https:\/\/www.youtube.com\/embed\/qxI98AvXRJo?si=tTyPO0tA_hejgD2U\" title=\"YouTube video player\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Start a New Instance of PostgreSQL and PostGIS<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">To begin, you need to start a PostgreSQL instance using Docker. Docker simplifies the process of running different PostgreSQL versions on your machine.<\/span><\/p>\n\n\n\n<h3><strong>Setting Up Docker Compose for PostgreSQL<\/strong><\/h3>\n\n\n\n<p>1. <span style=\"font-weight: 400;\">Go to the <\/span><a href=\"https:\/\/registry.hub.docker.com\/r\/postgis\/postgis\/\" rel=\"nofollow\"><span style=\"font-weight: 400;\">PostGIS Docker Hub page<\/span><\/a><span style=\"font-weight: 400;\"> and find the image for the desired PostgreSQL version, e.g., postgis\/postgis:16-3.4.<\/span><\/p>\n\n\n\n<p>2. <span style=\"font-weight: 400;\">Create necessary directories and set up the Docker Compose file:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">mkdir -p ~<span class=\"hljs-regexp\">\/opt\/<\/span>pg16\nmkdir -p ~<span class=\"hljs-regexp\">\/opt\/<\/span>pg16\/data\ncd ~<span class=\"hljs-regexp\">\/opt\/<\/span>pg16\nvim docker-compose.yml <\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>3. <span style=\"font-weight: 400;\">Add the following content to docker-compose.yml:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">version: <span class=\"hljs-string\">\"3.1\"<\/span>\nservices:\n  db:\n    image: postgis\/postgis:<span class=\"hljs-number\">16<\/span><span class=\"hljs-number\">-3.4<\/span>\n    container_name: pg<span class=\"hljs-number\">-16<\/span>-postgis\n    environment:\n      POSTGRES_PASSWORD: mobisoft<span class=\"hljs-comment\">#8976<\/span>\n    ports:\n      - <span class=\"hljs-string\">\"5416:5432\"<\/span>\n    volumes:\n      - ${PWD}\/data:\/<span class=\"hljs-keyword\">var<\/span>\/lib\/postgresql\/data<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\"><strong>Starting the PostgreSQL Server<\/strong><\/h3>\n\n\n\n<p>4. Start the PostgreSQL server:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">cd ~<span class=\"hljs-regexp\">\/opt\/<\/span>pg16\ndocker compose up -d\ndocker ps<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><span style=\"font-weight: 400;\">This will start the PostgreSQL server on <\/span><span style=\"font-weight: 400;\">localhost:5416<\/span><span style=\"font-weight: 400;\">.<\/span><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Install Only PostgreSQL Client<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">To interact with PostgreSQL, you need to install the PostgreSQL client:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">sudo apt update\nsudo apt install -y curl gpg gnupg2 software-properties-common apt-transport-https lsb-release ca-certificates\nsudo curl -fsSL https:<span class=\"hljs-comment\">\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo gpg --dearmor -o \/etc\/apt\/trusted.gpg.d\/postgresql.gpg<\/span>\n<span class=\"hljs-keyword\">echo<\/span> <span class=\"hljs-string\">\"deb http:\/\/apt.postgresql.org\/pub\/repos\/apt\/ `lsb_release -cs`-pgdg main\"<\/span> | sudo tee \/etc\/apt\/sources.<span class=\"hljs-keyword\">list<\/span>.d\/pgdg.<span class=\"hljs-keyword\">list<\/span>\nsudo apt update\nsudo apt install -y postgresql-client<span class=\"hljs-number\">-16<\/span>\npsql --version\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><span style=\"font-weight: 400;\">This installs the PostgreSQL client, allowing you to connect to the database server.<\/span><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Connect to PostgreSQL Using psql<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">psql<\/span><span style=\"font-weight: 400;\"> command-line tool is used to connect to a PostgreSQL server:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\">psql -h <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">hostname<\/span>&gt;<\/span> -p <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">port<\/span>&gt;<\/span> -U <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">db-username<\/span>&gt;<\/span> -W <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">db-name<\/span>&gt;<\/span>\n\npsql -h localhost -p 5416 -U postgres -W postgres<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Replace &lt;hostname&gt;, &lt;port&gt;, &lt;db-username&gt;, and &lt;db-name&gt; with your server&#8217;s details.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Basic Database Operations<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Create a New Database<\/strong><\/h3>\n\n\n\n<p><span style=\"font-weight: 400;\">To create a new database:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">CREATE DATABASE exampledb\nLC_COLLATE = <span class=\"hljs-string\">'en_US.UTF-8'<\/span>\nLC_CTYPE = <span class=\"hljs-string\">'en_US.UTF-8'<\/span>\nTEMPLATE template0;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"font-weight: 400;\">LC_COLLATE<\/span><span style=\"font-weight: 400;\"> specifies the collation order.<\/span><\/li>\n\n\n\n<li><span style=\"font-weight: 400;\">LC_CTYPE<\/span><span style=\"font-weight: 400;\"> specifies the character classification.<\/span><\/li>\n\n\n\n<li><span style=\"font-weight: 400;\">TEMPLATE<\/span> <span style=\"font-weight: 400;\">template0<\/span><span style=\"font-weight: 400;\"> ensures a clean database without preloaded data.<\/span><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/mobisoftinfotech.com\/contact-us?utm_source=blog_cta&#038;utm_campaign=master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial-cta1\"><noscript><img decoding=\"async\" width=\"855\" height=\"120\" style=\"border: 1px solid #000;\" src=\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/request-free-consultation_cta.png\" alt=\"Request a Free Consultation\" class=\"wp-image-30224\" title=\"Request a Free Consultation\"><\/noscript><img decoding=\"async\" width=\"855\" height=\"120\" style=\"border: 1px solid #000;\" src=\"data:image\/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20855%20120%22%3E%3C%2Fsvg%3E\" alt=\"Request a Free Consultation\" class=\"wp-image-30224 lazyload\" title=\"Request a Free Consultation\" data-src=\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/request-free-consultation_cta.png\"><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Show Databases<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">To list all databases:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">\\l<\/code><\/span><\/pre>\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Toggle Expanded Display Mode<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">To toggle expanded display mode in <\/span><span style=\"font-weight: 400;\">psql<\/span><span style=\"font-weight: 400;\">, use:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">\\x<\/code><\/span><\/pre>\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Change Database<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">To switch to another database:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">\\c exampledb<\/code><\/span><\/pre>\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Create Sample Data with pgbench<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">pgbench<\/span><span style=\"font-weight: 400;\"> is a benchmarking tool for PostgreSQL. To create sample data:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">sudo apt-<span class=\"hljs-keyword\">get<\/span> install postgresql-contrib-16\npgbench -h localhost -p 5416 -U postgres -i -s 10 exampledb\npgbench -h localhost -p 5416 -U postgres -c 10 -j 2 -T 60 exampledb\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Find Table Sizes<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">Connect to <\/span><span style=\"font-weight: 400;\">exampledb<\/span><span style=\"font-weight: 400;\"> using <\/span><span style=\"font-weight: 400;\">psql<\/span><span style=\"font-weight: 400;\">:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">psql -h localhost -p 5416 -U postgres -W exampledb\n<\/code><\/span><\/pre>\n\n\n<p><span style=\"font-weight: 400;\">To find the sizes of tables:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">SELECT\n    table_schema || <span class=\"hljs-string\">'.'<\/span> || table_name <span class=\"hljs-keyword\">AS<\/span> table_full_name,\n    pg_size_pretty(pg_total_relation_size(table_schema || <span class=\"hljs-string\">'.'<\/span> || table_name)) <span class=\"hljs-keyword\">AS<\/span> total_size\nFROM\n    information_schema.tables\nWHERE\n    table_type = <span class=\"hljs-string\">'BASE TABLE'<\/span>\n    <span class=\"hljs-keyword\">AND<\/span>\n    table_schema = <span class=\"hljs-string\">'public'<\/span>\nORDER BY\n    pg_total_relation_size(table_schema || <span class=\"hljs-string\">'.'<\/span> || table_name) DESC;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><span style=\"font-weight: 400;\">Adjust the <\/span><span style=\"font-weight: 400;\">table_schema<\/span><span style=\"font-weight: 400;\"> in the <\/span><span style=\"font-weight: 400;\">WHERE<\/span><span style=\"font-weight: 400;\"> clause if your tables are not in the <\/span><span style=\"font-weight: 400;\">public<\/span><span style=\"font-weight: 400;\"> schema.<\/span><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Run a Query and Save Output to CSV<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">To run a query and save its output to a CSV file:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">mkdir -p ~\/backup\npsql -h localhost -p <span class=\"hljs-number\">5416<\/span> -U postgres -W exampledb -c  <span class=\"hljs-string\">\"COPY (SELECT * FROM pgbench_accounts) TO STDOUT WITH CSV HEADER\"<\/span> &gt; ~\/backup\/pgbench_accounts.csv\nhead <span class=\"hljs-number\">-10<\/span> ~\/backup\/pgbench_accounts.csv\ncd ~\/backup\ntar -czvf pgbench_accounts.tar.gz pgbench_accounts.csv\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><span style=\"font-weight: 400;\">You can transfer the backup file using <\/span><span style=\"font-weight: 400;\">scp<\/span><span style=\"font-weight: 400;\">:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\">scp -i file.pem ubuntu@<span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">your_server_ip<\/span>&gt;<\/span>:\/home\/ubuntu\/backup\/pgbench_accounts.tar.gz ~\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Dump Entire Database<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">To dump the entire database:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">cd ~\/backup\npg_dump -h localhost -p 5416 -U postgres -W --format=c --file=exampledb.dump exampledb\n<\/code><\/span><\/pre>\n\n\n<p><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">&#8211;format=c<\/span><span style=\"font-weight: 400;\"> option specifies the custom format, which is already compressed.<\/span><\/p>\n\n\n\n<h3>Exclude Some Tables from Dump<\/h3>\n\n\n\n<p><span style=\"font-weight: 400;\">To exclude specific tables from the dump:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">pg_dump -h localhost -p 5416 -U postgres -W --format=c --exclude-table=pgbench_history --file=exampledb_without_history.dump exampledb\n<\/code><\/span><\/pre>\n\n\n<h3>Restore Backup to Another Database<\/h3>\n\n\n\n<p><span style=\"font-weight: 400;\">To restore a database from a dump:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">psql -h localhost -p <span class=\"hljs-number\">5416<\/span> -U postgres -W postgres -c  <span class=\"hljs-string\">\"CREATE DATABASE exampledb_frm_backup LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE template0;\"<\/span>\npg_restore -h localhost -p <span class=\"hljs-number\">5416<\/span> -d exampledb_frm_backup -U postgres -W -F c exampledb.dump<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/mobisoftinfotech.com\/resources\/mguide\/how-to-setup-postgresql-server-development-deployment\"><noscript><img decoding=\"async\" width=\"855\" height=\"120\" style=\"border: 1px solid #000;\" src=\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-postgresql-development_cta.png\" alt=\"Master PostgreSQL Development\" class=\"wp-image-30225\" title=\"Master PostgreSQL Development\"><\/noscript><img decoding=\"async\" width=\"855\" height=\"120\" style=\"border: 1px solid #000;\" src=\"data:image\/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20855%20120%22%3E%3C%2Fsvg%3E\" alt=\"Master PostgreSQL Development\" class=\"wp-image-30225 lazyload\" title=\"Master PostgreSQL Development\" data-src=\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-postgresql-development_cta.png\"><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Check Query Plans<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">To check query plans, use the <\/span><span style=\"font-weight: 400;\">EXPLAIN<\/span><span style=\"font-weight: 400;\"> command. Here is an example:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">SELECT\n    h.bid <span class=\"hljs-keyword\">AS<\/span> branch_id,\n    b.bbalance <span class=\"hljs-keyword\">AS<\/span> branch_balance,\n    h.aid <span class=\"hljs-keyword\">AS<\/span> account_id,\n    a.abalance <span class=\"hljs-keyword\">AS<\/span> account_balance,\n    h.tid <span class=\"hljs-keyword\">AS<\/span> teller_id,\n    t.tbalance <span class=\"hljs-keyword\">AS<\/span> teller_balance,\n    h.delta <span class=\"hljs-keyword\">AS<\/span> transaction_amount,\n    h.mtime <span class=\"hljs-keyword\">AS<\/span> transaction_time\nFROM\n    pgbench_history h\nJOIN\n    pgbench_accounts a ON h.aid = a.aid\nJOIN\n    pgbench_branches b ON h.bid = b.bid\nJOIN\n    pgbench_tellers t ON h.tid = t.tid\nORDER BY\n    h.mtime DESC\nLIMIT <span class=\"hljs-number\">10<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><span style=\"font-weight: 400;\">Minify the query and get the plan:<\/span><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">psql -h localhost -p <span class=\"hljs-number\">5416<\/span> -U postgres -W -d exampledb -qAt -c <span class=\"hljs-string\">\"EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT h.bid AS branch_id, b.bbalance AS branch_balance, h.aid AS account_id, a.abalance AS account_balance, h.tid AS teller_id, t.tbalance AS teller_balance, h.delta AS transaction_amount, h.mtime AS transaction_time FROM pgbench_history h JOIN pgbench_accounts a ON h.aid = a.aid JOIN pgbench_branches b ON h.bid = b.bid JOIN pgbench_tellers t ON h.tid = t.tid ORDER BY h.mtime DESC LIMIT 10;\"<\/span> &gt; query_plan.json\n\ncat query_plan.json<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><span style=\"font-weight: 400;\">Visualize the query plan at <\/span><a href=\"https:\/\/tatiyants.com\/pev\/#\/plans\/new\" rel=\"nofollow\"><span style=\"font-weight: 400;\">PEV<\/span><\/a><span style=\"font-weight: 400;\">.<\/span><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><noscript><img decoding=\"async\" width=\"855\" height=\"581\" style=\"border: 1px solid #000;\" src=\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/visualize-postgresql-query-plan.png\" alt=\"Visualize PostgreSQL Query Plan\" class=\"wp-image-30226\" title=\"Visualize PostgreSQL Query Plan\"><\/noscript><img decoding=\"async\" width=\"855\" height=\"581\" style=\"border: 1px solid #000;\" src=\"data:image\/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20855%20581%22%3E%3C%2Fsvg%3E\" alt=\"Visualize PostgreSQL Query Plan\" class=\"wp-image-30226 lazyload\" title=\"Visualize PostgreSQL Query Plan\" data-src=\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/visualize-postgresql-query-plan.png\"><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span style=\"font-weight: 400;\"><strong>Conclusion<\/strong><\/span><\/h2>\n\n\n\n<p><span style=\"font-weight: 400;\">In this tutorial, you have learned how to handle common PostgreSQL tasks from the command line, including starting a PostgreSQL instance with Docker, connecting to the database, creating and managing databases, handling backups, and examining query plans. Mastering these PostgreSQL command line utilities will enhance your efficiency in managing PostgreSQL databases, making it easier to develop, test, and deploy your applications. With practice, these tasks will become second nature, helping you to better leverage the power and flexibility of PostgreSQL in your projects.<\/span><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>For more insights on PostgreSQL in different environments, you can explore our other tutorials:<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/install-postgresql-and-postgis-in-gke\"><strong>How to Install PostgreSQL and PostGIS in Google Kubernetes Engine GKE<\/strong><\/a><strong>.<\/strong><\/li>\n\n\n\n<li><a href=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/important-gotchas-about-azure-database-for-postgresql\"><strong>Important Gotchas and Limitations About Azure Database for PostgreSQL<\/strong><\/a><strong>.<\/strong><\/li>\n<\/ul>\n\n\n<div class=\"related-posts-section\"><h2>Related Posts<\/h2><ul class=\"related-posts-list\"><li><a href=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/healthcare-automation-trends-in-2023\">Healthcare Automation Trends Refining Digital Acceleration in 2023<\/a><\/li><li><a href=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/best-apple-watch-apps\">Top 25 Apps That Everybody Needs on Their Apple Watch<\/a><\/li><li><a href=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/apple-wwdc-event-22-highlights-day-4\">Apple WWDC Event 2022: A Synopsis from Day 4<\/a><\/li><li><a href=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/kotlin-for-android-development\">Kotlin for Android Development for Your Next Application Venture<\/a><\/li><li><a href=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/gdpr-compliance-checklist-for-mobile-app-developers\">A Comprehensive GDPR Compliance Checklist For Mobile App Developers<\/a><\/li><li><a href=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/serverless-computing-deploy-applications-without-fiddling-with-servers\">Serverless Computing: Deploy Applications Without Fiddling With Servers<\/a><\/li><\/ul><\/div>\n\n\n<div class=\"modern-author-card\">\n    <div class=\"author-card-content\">\n        <div class=\"author-info-section\">\n            <div class=\"author-avatar\">\n                <noscript><img decoding=\"async\" src=\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2022\/04\/Pritam1.jpg\" alt=\"Pritam Barhate\"><\/noscript><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" alt=\"Pritam Barhate\" data-src=\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2022\/04\/Pritam1.jpg\" class=\" lazyload\">\n            <\/div>\n            <div class=\"author-details\">\n                <h3 class=\"author-name\">Pritam Barhate<\/h3>\n                <p class=\"author-title\">Head of Technology Innovation<\/p>\n                <a href=\"javascript:void(0);\" class=\"read-more-link read-more-btn\" onclick=\"toggleAuthorBio(this); return false;\">Read more <noscript><img decoding=\"async\" src=\"\/assets\/images\/blog\/Vector.png\" alt=\"expand\" class=\"read-more-arrow down-arrow\"><\/noscript><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" alt=\"expand\" class=\"read-more-arrow down-arrow lazyload\" data-src=\"\/assets\/images\/blog\/Vector.png\"><\/a>\n                <div class=\"author-bio-expanded\">\n                    <p>Pritam Barhate, with an experience of 14+ years in technology, heads Technology Innovation at <a href=\"https:\/\/mobisoftinfotech.com\" target=\"_blank\" rel=\"noopener\">Mobisoft Infotech<\/a>. He has a rich experience in design and development. He has been a consultant for a variety of industries and startups. At Mobisoft Infotech, he primarily focuses on technology resources and develops the most advanced solutions.<\/p>\n                    <div class=\"author-social-links\">\n                        <div class=\"social-icon\">\n                            <a href=\"https:\/\/www.linkedin.com\/in\/pritam-barhate-90b93414\/\" target=\"_blank\" rel=\"nofollow noopener\"><i class=\"icon-sprite linkedin\"><\/i><\/a>\n                            <a href=\"https:\/\/twitter.com\/pritambarhate\" target=\"_blank\" rel=\"nofollow noopener\"><i class=\"icon-sprite twitter\"><\/i><\/a>\n                        <\/div>\n                    <\/div>\n                    <a href=\"javascript:void(0);\" class=\"read-more-link read-less-btn\" onclick=\"toggleAuthorBio(this); return false;\" style=\"display: none;\">Read less <noscript><img decoding=\"async\" src=\"\/assets\/images\/blog\/Vector.png\" alt=\"collapse\" class=\"read-more-arrow up-arrow\"><\/noscript><img decoding=\"async\" src=\"data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7\" alt=\"collapse\" class=\"read-more-arrow up-arrow lazyload\" data-src=\"\/assets\/images\/blog\/Vector.png\"><\/a>\n                <\/div>\n            <\/div>\n        <\/div>\n        <div class=\"share-section\">\n            <span class=\"share-label\">Share Article<\/span>\n            <div class=\"social-share-buttons\">\n                <a href=\"https:\/\/www.facebook.com\/sharer\/sharer.php?u=https%3A%2F%2Fmobisoftinfotech.com%2Fresources%2Fblog%2Fmaster-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\" target=\"_blank\" class=\"share-btn facebook-share\"><i class=\"fa fa-facebook-f\"><\/i><\/a>\n                <a href=\"https:\/\/www.linkedin.com\/sharing\/share-offsite\/?url=https%3A%2F%2Fmobisoftinfotech.com%2Fresources%2Fblog%2Fmaster-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\" target=\"_blank\" class=\"share-btn linkedin-share\"><i class=\"fa fa-linkedin\"><\/i><\/a>\n            <\/div>\n        <\/div>\n    <\/div>\n<\/div>\n\n\n\n<script type=\"application\/ld+json\">\n{\n   \"@context\":\"https:\/\/schema.org\",\n   \"@type\":\"ImageObject\",\n   \"contentUrl\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png\",\n   \"url\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"name\":\"Using PgSQL Command Line Utilities\",\n   \"caption\":\"Master PostgreSQL Command Line Utilities\",\n   \"description\":\" Learn how to master PostgreSQL command line utilities like psql, pg_dump, and pg_restore with our comprehensive tutorial.\",\n   \"license\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"acquireLicensePage\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"creditText\":\"Mobisoft Infotech\",\n   \"copyrightNotice\":\"Mobisoft Infotech\",\n   \"creator\":{\n      \"@type\":\"Organization\",\n      \"name\":\"Mobisoft Infotech\"\n   },\n   \"thumbnail\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png\"\n},\n{\n   \"@type\":\"ImageObject\",\n   \"contentUrl\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/request-free-consultation_cta.png\",\n   \"url\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"name\":\"Request a Free Consultation\",\n   \"caption\":\"Contact Us for Expert PostgreSQL Consultation\",\n   \"description\":\"Contact us today to schedule a free consultation with our PostgreSQL experts.\",\n   \"license\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"acquireLicensePage\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"creditText\":\"Mobisoft Infotech\",\n   \"copyrightNotice\":\"Mobisoft Infotech\",\n   \"creator\":{\n      \"@type\":\"Organization\",\n      \"name\":\"Mobisoft Infotech\"\n   },\n   \"thumbnail\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/request-free-consultation_cta.png\"\n},\n{\n   \"@type\":\"ImageObject\",\n   \"contentUrl\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-postgresql-development_cta.png\",\n   \"url\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"name\":\"Secure Your API Today! Request a Free Security Consultation\",\n   \"caption\":\" Master PostgreSQL Development\",\n   \"description\":\"Discover more PostgreSQL development tips and tutorials on our blog.\",\n   \"license\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"acquireLicensePage\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"creditText\":\"Mobisoft Infotech\",\n   \"copyrightNotice\":\"Mobisoft Infotech\",\n   \"creator\":{\n      \"@type\":\"Organization\",\n      \"name\":\"Mobisoft Infotech\"\n   },\n   \"thumbnail\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-postgresql-development_cta.png\"\n},\n{\n   \"@type\":\"ImageObject\",\n   \"contentUrl\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/visualize-postgresql-query-plan.png\",\n   \"url\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"name\":\"Visualize PostgreSQL Query Plan\",\n   \"caption\":\"Visualizing PostgreSQL Query Plans\",\n   \"description\":\"Learn how to visualize PostgreSQL query plans using PEV (PostgreSQL Explain Visualizer) in our detailed tutorial.\",\n   \"license\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"acquireLicensePage\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\",\n   \"creditText\":\"Mobisoft Infotech\",\n   \"copyrightNotice\":\"Mobisoft Infotech\",\n   \"creator\":{\n      \"@type\":\"Organization\",\n      \"name\":\"Mobisoft Infotech\"\n   },\n   \"thumbnail\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/visualize-postgresql-query-plan.png\"\n}\n<\/script>\n<script type=\"application\/ld+json\">\n{\n  \"@context\": \"\nhttps:\/\/schema.org\"\n,\n  \"@type\": \"BlogPosting\",\n  \"mainEntityOfPage\": {\n    \"@type\": \"WebPage\",\n    \"@id\": \"\nhttps:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\/\"\n  },\n  \"headline\": \"Using PgSQL Command Line Utilities: psql, pg_dump, and pg_restore Tutorial\",\n  \"description\": \"Master PgSQL command line utilities\u2014psql, pg_dump, pg_restore\u2014with our step-by-step tutorial. Ideal for developers and database admins.\",\n  \"image\": \"\nhttps:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png\"\n,  \n  \"author\": {\n    \"@type\": \"Organization\",\n    \"name\": \"Pritam Barhate\",\n    \"url\": \"\nhttps:\/\/www.linkedin.com\/in\/pritam-barhate-90b93414\/\"\n  },  \n  \"publisher\": {\n    \"@type\": \"Organization\",\n    \"name\": \"Mobisoft Infotech Pvt Ltd\",\n    \"logo\": {\n      \"@type\": \"ImageObject\",\n      \"url\": \"\nhttps:\/\/cdn.mobisoftinfotech.com\/assets\/images\/MI_Logo.svg\"\n    }\n  },\n  \"datePublished\": \"2024-07-17\"\n}\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Introduction PostgreSQL, also known as Postgres, is a powerful, open-source relational database management system that emphasizes extensibility and SQL compliance. For developers and database administrators, efficiently managing PostgreSQL from the command line is a crucial skill. This tutorial will guide you through common PostgreSQL command line utilities using the command line on Ubuntu 22.04, leveraging [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":30223,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_s2mail":"","footnotes":""},"categories":[286],"tags":[4258,4259,4256,4257,4261,4255,4251,3661,4253,4260,4252,4263,4262,767,4254],"class_list":["post-30202","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-docker-postgresql","tag-pgbench","tag-pg_dump","tag-pg_restore","tag-postgresql-backups","tag-postgresql-client","tag-postgresql-command-line-utilities","tag-postgresql-database","tag-postgresql-from-the-command-line","tag-postgresql-instance","tag-postgresql-on-ubuntu-22-04","tag-postgresql-query-plans","tag-postgresql-restore","tag-postgresql-server","tag-postgresql-versions"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Master PSQL Command Line: psql, pg_dump, pg_restore Tutorial<\/title>\n<meta name=\"description\" content=\"Master PgSQL command line utilities\u2014psql, pg_dump, pg_restore\u2014with our step-by-step tutorial. Ideal for developers and database admins.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Master PSQL Command Line: psql, pg_dump, pg_restore Tutorial\" \/>\n<meta property=\"og:description\" content=\"Master PgSQL command line utilities\u2014psql, pg_dump, pg_restore\u2014with our step-by-step tutorial. Ideal for developers and database admins.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\" \/>\n<meta property=\"og:site_name\" content=\"Mobisoft Infotech\" \/>\n<meta property=\"article:published_time\" content=\"2024-07-17T12:33:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-12-30T11:54:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/og-Using-PgSQL-Command-Line-utilities.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1000\" \/>\n\t<meta property=\"og:image:height\" content=\"525\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Pritam Barhate\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Pritam Barhate\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#article\",\"isPartOf\":{\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\"},\"author\":{\"name\":\"Pritam Barhate\",\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/#\/schema\/person\/fa762036b3364f26abeea146c01487ee\"},\"headline\":\"Using PgSQL Command Line Utilities: psql, pg_dump, and pg_restore Tutorial\",\"datePublished\":\"2024-07-17T12:33:02+00:00\",\"dateModified\":\"2025-12-30T11:54:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\"},\"wordCount\":594,\"image\":{\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#primaryimage\"},\"thumbnailUrl\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png\",\"keywords\":[\"Docker PostgreSQL\",\"pgbench\",\"pg_dump\",\"pg_restore\",\"PostgreSQL backups\",\"PostgreSQL client\",\"PostgreSQL command line utilities\",\"PostgreSQL Database\",\"PostgreSQL from the command line\",\"PostgreSQL instance\",\"PostgreSQL on Ubuntu 22.04\",\"PostgreSQL query plans\",\"PostgreSQL restore\",\"PostgreSQL Server\",\"PostgreSQL versions\"],\"articleSection\":[\"Blog\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\",\"url\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\",\"name\":\"Master PSQL Command Line: psql, pg_dump, pg_restore Tutorial\",\"isPartOf\":{\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#primaryimage\"},\"image\":{\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#primaryimage\"},\"thumbnailUrl\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png\",\"datePublished\":\"2024-07-17T12:33:02+00:00\",\"dateModified\":\"2025-12-30T11:54:42+00:00\",\"author\":{\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/#\/schema\/person\/fa762036b3364f26abeea146c01487ee\"},\"description\":\"Master PgSQL command line utilities\u2014psql, pg_dump, pg_restore\u2014with our step-by-step tutorial. Ideal for developers and database admins.\",\"breadcrumb\":{\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#primaryimage\",\"url\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png\",\"contentUrl\":\"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png\",\"width\":855,\"height\":392,\"caption\":\"Using PgSQL Command Line Utilities\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/mobisoftinfotech.com\/resources\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using PgSQL Command Line Utilities: psql, pg_dump, and pg_restore Tutorial\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/#website\",\"url\":\"https:\/\/mobisoftinfotech.com\/resources\/\",\"name\":\"Mobisoft Infotech\",\"description\":\"Discover Mobility\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/mobisoftinfotech.com\/resources\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/mobisoftinfotech.com\/resources\/#\/schema\/person\/fa762036b3364f26abeea146c01487ee\",\"name\":\"Pritam Barhate\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0e481c7ce54b3567ac70ddfc493523eefce0bdc3ee69fd2654f8f60a79e2f178?s=96&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0e481c7ce54b3567ac70ddfc493523eefce0bdc3ee69fd2654f8f60a79e2f178?s=96&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0e481c7ce54b3567ac70ddfc493523eefce0bdc3ee69fd2654f8f60a79e2f178?s=96&r=g\",\"caption\":\"Pritam Barhate\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Master PSQL Command Line: psql, pg_dump, pg_restore Tutorial","description":"Master PgSQL command line utilities\u2014psql, pg_dump, pg_restore\u2014with our step-by-step tutorial. Ideal for developers and database admins.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial","og_locale":"en_US","og_type":"article","og_title":"Master PSQL Command Line: psql, pg_dump, pg_restore Tutorial","og_description":"Master PgSQL command line utilities\u2014psql, pg_dump, pg_restore\u2014with our step-by-step tutorial. Ideal for developers and database admins.","og_url":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial","og_site_name":"Mobisoft Infotech","article_published_time":"2024-07-17T12:33:02+00:00","article_modified_time":"2025-12-30T11:54:42+00:00","og_image":[{"width":1000,"height":525,"url":"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/og-Using-PgSQL-Command-Line-utilities.png","type":"image\/png"}],"author":"Pritam Barhate","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Pritam Barhate","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#article","isPartOf":{"@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial"},"author":{"name":"Pritam Barhate","@id":"https:\/\/mobisoftinfotech.com\/resources\/#\/schema\/person\/fa762036b3364f26abeea146c01487ee"},"headline":"Using PgSQL Command Line Utilities: psql, pg_dump, and pg_restore Tutorial","datePublished":"2024-07-17T12:33:02+00:00","dateModified":"2025-12-30T11:54:42+00:00","mainEntityOfPage":{"@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial"},"wordCount":594,"image":{"@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#primaryimage"},"thumbnailUrl":"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png","keywords":["Docker PostgreSQL","pgbench","pg_dump","pg_restore","PostgreSQL backups","PostgreSQL client","PostgreSQL command line utilities","PostgreSQL Database","PostgreSQL from the command line","PostgreSQL instance","PostgreSQL on Ubuntu 22.04","PostgreSQL query plans","PostgreSQL restore","PostgreSQL Server","PostgreSQL versions"],"articleSection":["Blog"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial","url":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial","name":"Master PSQL Command Line: psql, pg_dump, pg_restore Tutorial","isPartOf":{"@id":"https:\/\/mobisoftinfotech.com\/resources\/#website"},"primaryImageOfPage":{"@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#primaryimage"},"image":{"@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#primaryimage"},"thumbnailUrl":"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png","datePublished":"2024-07-17T12:33:02+00:00","dateModified":"2025-12-30T11:54:42+00:00","author":{"@id":"https:\/\/mobisoftinfotech.com\/resources\/#\/schema\/person\/fa762036b3364f26abeea146c01487ee"},"description":"Master PgSQL command line utilities\u2014psql, pg_dump, pg_restore\u2014with our step-by-step tutorial. Ideal for developers and database admins.","breadcrumb":{"@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#primaryimage","url":"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png","contentUrl":"https:\/\/mobisoftinfotech.com\/resources\/wp-content\/uploads\/2024\/07\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial.png","width":855,"height":392,"caption":"Using PgSQL Command Line Utilities"},{"@type":"BreadcrumbList","@id":"https:\/\/mobisoftinfotech.com\/resources\/blog\/master-pgsql-command-line-psql-pg-dump-pg-restore-tutorial#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/mobisoftinfotech.com\/resources\/"},{"@type":"ListItem","position":2,"name":"Using PgSQL Command Line Utilities: psql, pg_dump, and pg_restore Tutorial"}]},{"@type":"WebSite","@id":"https:\/\/mobisoftinfotech.com\/resources\/#website","url":"https:\/\/mobisoftinfotech.com\/resources\/","name":"Mobisoft Infotech","description":"Discover Mobility","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/mobisoftinfotech.com\/resources\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/mobisoftinfotech.com\/resources\/#\/schema\/person\/fa762036b3364f26abeea146c01487ee","name":"Pritam Barhate","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0e481c7ce54b3567ac70ddfc493523eefce0bdc3ee69fd2654f8f60a79e2f178?s=96&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0e481c7ce54b3567ac70ddfc493523eefce0bdc3ee69fd2654f8f60a79e2f178?s=96&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0e481c7ce54b3567ac70ddfc493523eefce0bdc3ee69fd2654f8f60a79e2f178?s=96&r=g","caption":"Pritam Barhate"}}]}},"_links":{"self":[{"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/posts\/30202","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/comments?post=30202"}],"version-history":[{"count":47,"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/posts\/30202\/revisions"}],"predecessor-version":[{"id":46284,"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/posts\/30202\/revisions\/46284"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/media\/30223"}],"wp:attachment":[{"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/media?parent=30202"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/categories?post=30202"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mobisoftinfotech.com\/resources\/wp-json\/wp\/v2\/tags?post=30202"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}