{"id":786,"date":"2020-04-10T13:00:25","date_gmt":"2020-04-10T18:00:25","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=786"},"modified":"2020-04-10T13:01:31","modified_gmt":"2020-04-10T18:01:31","slug":"backup-postgresql-to-aws-s3-in-linux-centos","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/backup-postgresql-to-aws-s3-in-linux-centos\/","title":{"rendered":"Backup PostgreSQL to AWS s3 in Linux CentOS"},"content":{"rendered":"\n

This post walks you through how to backup a PostgreSQL database to an AWS s3 bucket.<\/p>\n\n\n\n

There are a few installations we’ll need to make before allowing our on-prem Postgres server to communicate with AWS.<\/p>\n\n\n\n

Install pip<\/span><\/strong><\/p>\n\n\n\n

  1. Use the curl<\/code> command to download the installation script. The following command uses the -O<\/code> (uppercase “O”) parameter to specify that the downloaded file is to be stored in the current folder using the same name it has on the remote host:

    curl -O https:\/\/bootstrap.pypa.io\/get-pip.py<\/strong> <\/code>
    <\/li>
  2. Run the script with Python to download and install the latest version of pip<\/code> and other required support packages:

    python36 get-pip.py --user<\/code><\/strong>

    When you include the --user<\/code> switch, the script installs pip<\/code> to the path ~\/.local\/bin<\/code>.
    <\/li>
  3. Ensure the folder that contains pip<\/code> is part of your PATH<\/code> variable.

    ls -a ~<\/code><\/strong>
    <\/li>
  4. Add an export command at the end of your profile script that’s similar to the following example.

    source ~\/<\/strong>.bash_profile<\/code>
    <\/li>
  5. Now you can test to verify that pip<\/code> is installed correctly.

    pip3 --version<\/code><\/strong>
    <\/li><\/ol>\n\n\n\n

    Install the AWS CLI with pip<\/span><\/strong><\/p>\n\n\n\n

    1. Use pip<\/code> to install the AWS CLI.

      pip3 install awscli --upgrade --user<\/code><\/strong>
      <\/li>
    2. Verify that the AWS CLI installed correctly.

      aws --version<\/code><\/strong><\/li><\/ol>\n\n\n\n

      Now that we have AWS CLI installed, we can configure our new client. You will need AWS Access Key ID, AWS Secret Access Key, Default Region Name and Default Output Format This information you can go to the IAM AWS Section.<\/p>\n\n\n\n

      aws configure<\/code><\/strong><\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      To view your s3 buckets use the following:<\/p>\n\n\n\n

      aws s3 ls<\/code><\/strong><\/p>\n\n\n\n

      Now that AWS is configured and we can view our s3 buckets, let’s make a backup:<\/p>\n\n\n\n

      PGPASSWORD=\"password\" .\/pg_dump --no-owner -h localhost -U databasename > ~\/databasename.sql<\/code><\/strong><\/p>\n\n\n\n

      To view the backup file use the following:<\/p>\n\n\n\n

      cd \/root
      dir<\/code><\/strong><\/p>\n\n\n\n

      Now that we have a backup, let’s create an AWS s3 bucket to store them in:<\/p>\n\n\n\n

      aws s3api create-bucket --bucket postgres-backups --region us-west-2 --create-bucket-configuration LocationConstraint=us-west-2<\/code><\/strong><\/p>\n\n\n\n

      Back in AWS, you can see the new bucket:<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      Once the new bucket has been created, let’s push the backup we took earlier to this bucket.<\/p>\n\n\n\n

      aws s3 cp databasename.sql s3:\/\/postgres-backups\/<\/code><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

      This post walks you through how to backup a PostgreSQL database to an AWS s3 bucket. There are a few installations we’ll need to make before allowing our on-prem Postgres server to communicate with AWS. Install pip Use the curl command to download the installation script. The following command uses the -O (uppercase “O”) parameter to specify that the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[129],"tags":[],"class_list":["post-786","post","type-post","status-publish","format-standard","hentry","category-aws"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/786","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=786"}],"version-history":[{"count":4,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/786\/revisions"}],"predecessor-version":[{"id":793,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/786\/revisions\/793"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=786"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=786"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=786"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}