PostgreSQL comes with two command-line utilities for dumping and then restoring a database — pg_dump(Backup Database) and pg_restore(Restore Database), respectively
— first switch to postgres user
# su – postgres
Postgresql command for backup database
pg_dump -v -d databasename > database-dump-filename.dump
pg_dump -U db_user -W -F t db_name > /tmp/dump_name.tar
Here we used the following options:
-U to specify which user will connect to the PostgreSQL database server.
-W or –password will force pg_dump to prompt for a password before connecting to the server.
-F is used to specify the format of the output file, which can be one of the following:
p – plain-text SQL script
c – custom-format archive
d – directory-format archive
t – tar-format archive
Postgresql Create New UTF-8 Database,
below command direct can fire from postgres user
createdb -E UTF8 -T template0 –locale=en_US.utf8 <dbname>
To create database from psql terminal
CREATE DATABASE “snippetbucket_db”
WITH OWNER “postgres”
LC_COLLATE = ‘en_US.UTF-8’
LC_CTYPE = ‘en_US.UTF-8’
Postgresql command to restore database
pg_restore -d databasename database-dump-filename.dump
With pg_restore you have various options available, for example:
-C to create a database before restoring into it,
-c to drop database objects before recreating them,
-e exit if an error has encountered,
-F format to specify the format of the archive.
Restore a database with psql
psql -U db_user db_name < /tmp/dump_name.sql
Odoo: Restore manual dump backup.
pg_restore -v -O -x -n public -d oe12_sb_2019_second sb-2019-5-20.dump
dump file: sb-2019-5-20.dump
-v -O -x -n public these are key important parameters to restore database.
Use the following command to compress an entire directory or a single file on Linux. It will also compress every other directory inside a directory you specify – in other words, it works recursively.
tar -czvf name-of-archive.tar.gz /path/to/directory-or-file
Here’s what those switches actually mean:
- -c: Create an archive.
- -z: Compress the archive with gzip.
- -v: Display progress in the terminal while creating the archive, also known as “verbose” mode. The v is always optional in these commands, but it’s helpful.
- -f: Allows you to specify the filename of the archive.
If you have a directory named ‘data’ in the current directory and you want to save it to a file named archive.tar.gz , you would run the following command:
tar -czvf archive.tar.gz data
If you have a directory at /usr/local/something on the current system and you want to compress it to a file named archive.tar.gz , you would run the following command:
tar -czvf archive.tar.gz /usr/local/something
Extract an Archive
Once you have an archive, you can extract it with the tar command. The following command will extract the contents of archive.tar.gz to the current directory.
tar -xzvf archive.tar.gz
It’s the same as the archive creation command we used above, except the -x switch replaces the -c switch. This specifies you want to extract an archive instead of create one.
You may want to extract the contents of the archive to a specific directory. You can do so by appending the -C switch to the end of the command. For example, the following command will extract the contents of the archive.tar.gz file to the /tmp directory.
tar -xzvf archive.tar.gz -C /tmp