PostgreSQL database backup with pg_dump by using .pgpass file

 It is better not to use password in commands so for taking backups from postgresql database we can store credentials in special file and give it to command so with this way it can be possible to run commands securely and password will be read from .pgpass file automatically

/usr/bin/pg_dump -F c -h localhost -U yourdbuser yourdbname > /path_$(date '+%d%m%Y_%H%M%S').sql.zip



cat /home/yourOSuser/.pgpass 

localhost:5432:yourdbname:yourdbuser:yourdbuserpassword


if order to make it work also do not forget to change connection method


/var/lib/pgsql/14/data/pg_hba.conf file


# "local" is for Unix domain socket connections only

local   all         all    peer  ->>>> change this to md5 to login with user


and restart your postgres database to reflect changes


sudo service postgresql-14.service status




how about restore

PS: this is not regular zip file do not try to unzip this!



create database first


sudo -u postgres psql


postgres=# create database yourdbname;


postgres=# GRANT ALL PRIVILEGES ON DATABASE yourdbname to yourdbuser;


pg_restore -h localhost -U yourdbuser -C -d yourdbname /path_$(date '+%d%m%Y_%H%M%S').sql.zip

Comments

Post a Comment

Popular posts from this blog

Pyppeteer fix for BrowserError: Browser closed unexpectedly

if you are having an error related with Pyppeteer   BrowserError: Browser closed unexpectedly   is just the error you get when Chrome crashes for whatever reason. It would be nice if pyppeteer printed out the error, but it doesn't. To track things down, it's helpful to pull up the exact command that pyppeteer runs. You can do that this way: from pyppeteer.launcher import Launcher ' '.join(Launcher().cmd) run output from command line and find missing libraries and install it.
Read more

How to add pagination to django comments for your model

First step is import Comment and ContentType to your view like below from django.contrib.comments.models import Comment from django.contrib.contenttypes.models import ContentType Second step is defining your model which you want to paginate its comments content_type_id= ContentType.objects.get_for_model( YOUR_MODEL_NAME ) Last get comments of specific instance of your model class with defining object_pk as filter parameter comments = Comment.objects.filter(content_type=content_type_id,object_pk=quote_id) here is complete view code from django.core.paginator import Paginator, InvalidPage, EmptyPage from django.contrib.comments.models import Comment from django.contrib.contenttypes.models import ContentType def quote_show_page(request,quote_id): quote = get_object_or_404(Quote, id=quote_id) content_type_id=ContentType.objects.get_for_model(Quote) comments = Comment.objects.filter(content_type=content_type_id,object_pk=quote_id) paginator = Paginator(comments, 5) # Show 5 comments pe...
Read more

Django project and Perfect setup of nginx config for www to nonwww and http to https and ip to domain redirects

Here I am sharing good setup of nginx config for following requirements. before following config you need to create 2 A record for both domain to ip and www.domain to ip in your dns provider. also for django settings file you can add domain and www.domain to ALLOWED_HOSTS http to https www to nonwww https://ip to domain http://ip to domain redirects place following config to your sites-enabled config file server {   listen 80;   listen 443 ssl;   server_name yourserverip;   ssl_certificate /home/yourdomain.com.crt;   ssl_certificate_key /home/yourdomain.com.key;   add_header X-Frame-Options "SAMEORIGIN";   return 301 https://yourdomain.com$request_uri; } server {     listen 443 ssl;     server_name yourdomain.com;     ssl_certificate /home/yourdomain.com.crt;     ssl_certificate_key /home/yourdomain.com.key;     ssl_protocols TLSv1.2 TLSv1.1 TLSv1;     ssl_prefer_server_ciphers on;   ...
Read more