{"id":350,"date":"2024-05-06T17:05:00","date_gmt":"2024-05-06T17:05:00","guid":{"rendered":"http:\/\/77interactive.com\/?p=350"},"modified":"2024-05-07T15:12:37","modified_gmt":"2024-05-07T15:12:37","slug":"backup-script-in-pg","status":"publish","type":"post","link":"http:\/\/77interactive.com\/?p=350","title":{"rendered":"Backup Script in PG"},"content":{"rendered":"\n<p>The following script is for backing up a postgres database<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import subprocess\r\nimport psycopg2\r\nfrom datetime import datetime\r\nimport os\r\n\r\n# PostgreSQL connection parameters\r\ndb_params = {\r\n    \"dbname\": \"postgres\",  # change to your database name if different\r\n    \"user\": \"your_username\",\r\n    \"password\": \"your_password\",\r\n    \"host\": \"localhost\"\r\n}\r\n\r\n# Directory to store the backups\r\nbackup_dir = \"\/mnt\/backup\/\"\r\nos.makedirs(backup_dir, exist_ok=True)\r\n\r\ndef get_databases(connection_parameters):\r\n    \"\"\"Retrieve a list of database names.\"\"\"\r\n    conn = None\r\n    databases = &#91;]\r\n    try:\r\n        conn = psycopg2.connect(**connection_parameters)\r\n        cur = conn.cursor()\r\n        cur.execute(\"SELECT datname FROM pg_database WHERE datistemplate = false;\")\r\n        databases = &#91;db&#91;0] for db in cur.fetchall()]\r\n        cur.close()\r\n    except (Exception, psycopg2.DatabaseError) as error:\r\n        print(error)\r\n    finally:\r\n        if conn is not closed:\r\n            conn.close()\r\n    return databases\r\n\r\ndef backup_database(db_name, backup_directory):\r\n    \"\"\"Backup a single database and zip the backup file.\"\"\"\r\n    current_date = datetime.now().strftime('%Y%m%d')\r\n    backup_file = os.path.join(backup_directory, f\"{db_name}.sql\")\r\n    zip_file = f\"{current_date}-{db_name}.zip\"\r\n    \r\n    # Backup database to SQL file\r\n    subprocess.run(f\"pg_dump -U {db_params&#91;'user']} -w {db_name} > {backup_file}\", shell=True, check=True)\r\n    \r\n    # Compress the SQL file\r\n    subprocess.run(f\"zip -j {backup_dir}{zip_file} {backup_file}\", shell=True, check=True)\r\n    \r\n    # Remove the original SQL file\r\n    os.remove(backup_file)\r\n\r\nif __name__ == \"__main__\":\r\n    databases = get_databases(db_params)\r\n    for db_name in databases:\r\n        print(f\"Backing up database: {db_name}\")\r\n        backup_database(db_name, backup_dir)\r\n    print(\"Backup process completed.\")\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Backup Via .env file<\/h2>\n\n\n\n<p>The following script uses a .env instead of a .pgpass file<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import os\r\nimport psycopg2\r\nfrom datetime import datetime\r\nimport subprocess\r\nfrom dotenv import load_dotenv\r\n\r\n# Load environment variables\r\nload_dotenv()\r\n\r\n# Database connection parameters\r\nDB_HOST = os.getenv('DB_HOST')\r\nDB_PORT = os.getenv('DB_PORT')\r\nDB_USER = os.getenv('DB_USER')\r\nDB_PASSWORD = os.getenv('DB_PASSWORD')\r\n\r\n# Directory to store the backups\r\nbackup_dir = \"\/mnt\/backup\/\"\r\nos.makedirs(backup_dir, exist_ok=True)\r\n\r\ndef get_databases():\r\n    \"\"\"Retrieve a list of database names.\"\"\"\r\n    conn = None\r\n    databases = &#91;]\r\n    try:\r\n        conn = psycopg2.connect(\r\n            dbname='postgres',  # Adjust if you use a different db for listing databases\r\n            user=DB_USER,\r\n            password=DB_PASSWORD,\r\n            host=DB_HOST,\r\n            port=DB_PORT\r\n        )\r\n        cur = conn.cursor()\r\n        cur.execute(\"SELECT datname FROM pg_database WHERE datistemplate = false;\")\r\n        databases = &#91;db&#91;0] for db in cur.fetchall()]\r\n        cur.close()\r\n    except (Exception, psycopg2.DatabaseError) as error:\r\n        print(error)\r\n    finally:\r\n        if conn is not None:\r\n            conn.close()\r\n    return databases\r\n\r\ndef backup_database(db_name):\r\n    \"\"\"Backup a single database and zip the backup file.\"\"\"\r\n    current_date = datetime.now().strftime('%Y%m%d')\r\n    backup_file = os.path.join(backup_dir, f\"{db_name}.sql\")\r\n    zip_file = f\"{current_date}-{db_name}.zip\"\r\n    \r\n    # Dump the database\r\n    dump_command = f\"pg_dump -h {DB_HOST} -p {DB_PORT} -U {DB_USER} -w {db_name} > {backup_file}\"\r\n    os.environ&#91;'PGPASSWORD'] = DB_PASSWORD\r\n    subprocess.run(dump_command, shell=True, check=True)\r\n    \r\n    # Compress the SQL file\r\n    zip_command = f\"zip -j {backup_dir}\/{zip_file} {backup_file}\"\r\n    subprocess.run(zip_command, shell=True, check=True)\r\n    \r\n    # Remove the original SQL file\r\n    os.remove(backup_file)\r\n\r\nif __name__ == \"__main__\":\r\n    databases = get_databases()\r\n    for db_name in databases:\r\n        print(f\"Backing up database: {db_name}\")\r\n        backup_database(db_name)\r\n    print(\"Backup process completed.\")\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The following script is for backing up a postgres database Backup Via .env file The following script uses a .env instead of a .pgpass file<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[17,18,3],"class_list":["post-350","post","type-post","status-publish","format-standard","hentry","category-backup","tag-postgres","tag-postgresql","tag-python"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Backup Script in PG - 77 Interactive<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/77interactive.com\/?p=350\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Backup Script in PG - 77 Interactive\" \/>\n<meta property=\"og:description\" content=\"The following script is for backing up a postgres database Backup Via .env file The following script uses a .env instead of a .pgpass file\" \/>\n<meta property=\"og:url\" content=\"http:\/\/77interactive.com\/?p=350\" \/>\n<meta property=\"og:site_name\" content=\"77 Interactive\" \/>\n<meta property=\"article:published_time\" content=\"2024-05-06T17:05:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-05-07T15:12:37+00:00\" \/>\n<meta name=\"author\" content=\"Rudy\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Rudy\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/77interactive.com\/?p=350#article\",\"isPartOf\":{\"@id\":\"http:\/\/77interactive.com\/?p=350\"},\"author\":{\"name\":\"Rudy\",\"@id\":\"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9\"},\"headline\":\"Backup Script in PG\",\"datePublished\":\"2024-05-06T17:05:00+00:00\",\"dateModified\":\"2024-05-07T15:12:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/77interactive.com\/?p=350\"},\"wordCount\":29,\"keywords\":[\"postgres\",\"postgresql\",\"python\"],\"articleSection\":[\"backup\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/77interactive.com\/?p=350\",\"url\":\"http:\/\/77interactive.com\/?p=350\",\"name\":\"Backup Script in PG - 77 Interactive\",\"isPartOf\":{\"@id\":\"http:\/\/77interactive.com\/#website\"},\"datePublished\":\"2024-05-06T17:05:00+00:00\",\"dateModified\":\"2024-05-07T15:12:37+00:00\",\"author\":{\"@id\":\"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9\"},\"breadcrumb\":{\"@id\":\"http:\/\/77interactive.com\/?p=350#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/77interactive.com\/?p=350\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/77interactive.com\/?p=350#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/77interactive.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Backup Script in PG\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/77interactive.com\/#website\",\"url\":\"http:\/\/77interactive.com\/\",\"name\":\"77 Interactive\",\"description\":\"Rudy&#039;s Code snippets\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/77interactive.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9\",\"name\":\"Rudy\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/77interactive.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e336b9aecd39b40691ff8ccfcd68506415072dbe8caffc0485b94a1bc22b774d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e336b9aecd39b40691ff8ccfcd68506415072dbe8caffc0485b94a1bc22b774d?s=96&d=mm&r=g\",\"caption\":\"Rudy\"},\"url\":\"http:\/\/77interactive.com\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Backup Script in PG - 77 Interactive","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":"http:\/\/77interactive.com\/?p=350","og_locale":"en_US","og_type":"article","og_title":"Backup Script in PG - 77 Interactive","og_description":"The following script is for backing up a postgres database Backup Via .env file The following script uses a .env instead of a .pgpass file","og_url":"http:\/\/77interactive.com\/?p=350","og_site_name":"77 Interactive","article_published_time":"2024-05-06T17:05:00+00:00","article_modified_time":"2024-05-07T15:12:37+00:00","author":"Rudy","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Rudy","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/77interactive.com\/?p=350#article","isPartOf":{"@id":"http:\/\/77interactive.com\/?p=350"},"author":{"name":"Rudy","@id":"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9"},"headline":"Backup Script in PG","datePublished":"2024-05-06T17:05:00+00:00","dateModified":"2024-05-07T15:12:37+00:00","mainEntityOfPage":{"@id":"http:\/\/77interactive.com\/?p=350"},"wordCount":29,"keywords":["postgres","postgresql","python"],"articleSection":["backup"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"http:\/\/77interactive.com\/?p=350","url":"http:\/\/77interactive.com\/?p=350","name":"Backup Script in PG - 77 Interactive","isPartOf":{"@id":"http:\/\/77interactive.com\/#website"},"datePublished":"2024-05-06T17:05:00+00:00","dateModified":"2024-05-07T15:12:37+00:00","author":{"@id":"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9"},"breadcrumb":{"@id":"http:\/\/77interactive.com\/?p=350#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/77interactive.com\/?p=350"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/77interactive.com\/?p=350#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/77interactive.com\/"},{"@type":"ListItem","position":2,"name":"Backup Script in PG"}]},{"@type":"WebSite","@id":"http:\/\/77interactive.com\/#website","url":"http:\/\/77interactive.com\/","name":"77 Interactive","description":"Rudy&#039;s Code snippets","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/77interactive.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9","name":"Rudy","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/77interactive.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/e336b9aecd39b40691ff8ccfcd68506415072dbe8caffc0485b94a1bc22b774d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e336b9aecd39b40691ff8ccfcd68506415072dbe8caffc0485b94a1bc22b774d?s=96&d=mm&r=g","caption":"Rudy"},"url":"http:\/\/77interactive.com\/?author=1"}]}},"_links":{"self":[{"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/posts\/350","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/77interactive.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=350"}],"version-history":[{"count":0,"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/posts\/350\/revisions"}],"wp:attachment":[{"href":"http:\/\/77interactive.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=350"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/77interactive.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=350"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/77interactive.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=350"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}