¿Como puedo restaurar una base de datos postgre con un script PHP?[Unresolved]

Answers:

•  

    2  

   

   

  Jorge Zabala Karma: 517

   
  Date: 24-10-2017 07:41:24 Revisa esta clase que encontre en https://www.phpclasses.org/package/4141-PHP-Backup-and-restore-PostgreSQL-databases-with-files.html , se ve interesante.
  
  pgBackupRestore2.class.php
  https://gist.github.com/ZeusAFK/4540f4c6808c8d0d2417389ce3c0d331
  
  example.php
  https://gist.github.com/ZeusAFK/d26281fe5ff3621c2a2b6fad3eb94266   Votes: 2 - Link answer
   
•  

    0  

   

   

  Ernesto Peimbert Karma: 5324

   
  Date: 25-10-2017 12:48:17 La mayoría de los ORM (Doctrine, Eloquent, etc.) te permiten generar la base de datos desde cero basado en los modelos que defines.
  
  Otra opción sería hacer migraciones con Phinx.   Votes: 0 - Link answer
   
•  

    0  

   

   

  Rodney Guillén Karma: 15

   
  Date: 30-10-2017 10:02:06 Este es un buen ejemplo, podrías acomodarlo a tus necesidades.
  En tu caso seria de utilizar el case del import y cambiar tu variable por la ruta del backup de tu base de datos postgresql.
  
  Saludos
  
  

  <?php
  // scrypt for backup and restore postgres database
  
  
  function dl_file($file){
     if (!is_file($file)) { die("<b>404 File not found!</b>"); }
     $len = filesize($file);
     $filename = basename($file);
     $file_extension = strtolower(substr(strrchr($filename,"."),1));
     $ctype="application/force-download";
     header("Pragma: public");
     header("Expires: 0");
     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
     header("Cache-Control: public");
     header("Content-Description: File Transfer");
     header("Content-Type: $ctype");
     $header="Content-Disposition: attachment; filename=".$filename.";";
     header($header );
     header("Content-Transfer-Encoding: binary");
     header("Content-Length: ".$len);
     @readfile($file);
     exit;
  }
  
  
  
  $action  = $_POST["actionButton"];
  $ficheiro=$_FILES["path"]["name"];
  switch ($action) {
      case "Import":
        $dbname = "teste"; //database name
        $dbconn = pg_pconnect("host=localhost port=5432 dbname=$dbname 
  user=postgres password=rularv"); //connectionstring
        if (!$dbconn) {
          echo "Can't connect.\n";
          exit;
        }
        $back = fopen($ficheiro,"r");
        $contents = fread($back, filesize($ficheiro));
        $res = pg_query(utf8_encode($contents));
        echo "Upload Ok";
        fclose($back);
    break;
    case "Export":
    $dbname = "MiradouroTest"; //database name
    $dbconn = pg_pconnect("host=localhost port=5432 dbname=$dbname 
  user=postgres password=rularv"); //connectionstring
    if (!$dbconn) {
      echo "Can't connect.\n";
    exit;
    }
    $back = fopen("$dbname.sql","w");
    $res = pg_query(" select relname as tablename
                      from pg_class where relkind in ('r')
                      and relname not like 'pg_%' and relname not like 
  'sql_%' order by tablename");
    $str="";
    while($row = pg_fetch_row($res))
    {
      $table = $row[0];
      $str .= "\n--\n";
      $str .= "-- Estrutura da tabela '$table'";
      $str .= "\n--\n";
      $str .= "\nDROP TABLE $table CASCADE;";
      $str .= "\nCREATE TABLE $table (";
      $res2 = pg_query("
      SELECT  attnum,attname , typname , atttypmod-4 , attnotnull 
  ,atthasdef ,adsrc AS def
      FROM pg_attribute, pg_class, pg_type, pg_attrdef WHERE 
  pg_class.oid=attrelid
      AND pg_type.oid=atttypid AND attnum>0 AND pg_class.oid=adrelid AND 
  adnum=attnum
      AND atthasdef='t' AND lower(relname)='$table' UNION
      SELECT attnum,attname , typname , atttypmod-4 , attnotnull , 
  atthasdef ,'' AS def
      FROM pg_attribute, pg_class, pg_type WHERE pg_class.oid=attrelid
      AND pg_type.oid=atttypid AND attnum>0 AND atthasdef='f' AND 
  lower(relname)='$table' ");                                             
      while($r = pg_fetch_row($res2))
      {
      $str .= "\n" . $r[1]. " " . $r[2];
       if ($r[2]=="varchar")
      {
      $str .= "(".$r[3] .")";
      }
      if ($r[4]=="t")
      {
      $str .= " NOT NULL";
      }
      if ($r[5]=="t")
      {
      $str .= " DEFAULT ".$r[6];
      }
      $str .= ",";
      }
      $str=rtrim($str, ",");  
      $str .= "\n);\n";
      $str .= "\n--\n";
      $str .= "-- Creating data for '$table'";
      $str .= "\n--\n\n";
  
      
      $res3 = pg_query("SELECT * FROM $table");
      while($r = pg_fetch_row($res3))
      {
        $sql = "INSERT INTO $table VALUES ('";
        $sql .= utf8_decode(implode("','",$r));
        $sql .= "');";
        $str = str_replace("''","NULL",$str);
        $str .= $sql;  
        $str .= "\n";
      }
      
       $res1 = pg_query("SELECT pg_index.indisprimary,
              pg_catalog.pg_get_indexdef(pg_index.indexrelid)
          FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
              pg_catalog.pg_index AS pg_index
          WHERE c.relname = '$table'
              AND c.oid = pg_index.indrelid
              AND pg_index.indexrelid = c2.oid
              AND pg_index.indisprimary");
      while($r = pg_fetch_row($res1))
      {
      $str .= "\n\n--\n";
      $str .= "-- Creating index for '$table'";
      $str .= "\n--\n\n";
      $t = str_replace("CREATE UNIQUE INDEX", "", $r[1]);
      $t = str_replace("USING btree", "|", $t);
      // Next Line Can be improved!!!
      $t = str_replace("ON", "|", $t);
      $Temparray = explode("|", $t);
      $str .= "ALTER TABLE ONLY ". $Temparray[1] . " ADD CONSTRAINT " . 
  $Temparray[0] . " PRIMARY KEY " . $Temparray[2] .";\n";
      }   
    }
    $res = pg_query(" SELECT
    cl.relname AS tabela,ct.conname,
     pg_get_constraintdef(ct.oid)
     FROM pg_catalog.pg_attribute a
     JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r')
     JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)
     JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND
     ct.confrelid != 0 AND ct.conkey[1] = a.attnum)
     JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND 
  clf.relkind = 'r')
     JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)
     JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND
     af.attnum = ct.confkey[1]) order by cl.relname ");
    while($row = pg_fetch_row($res))
    {
      $str .= "\n\n--\n";
      $str .= "-- Creating relacionships for '".$row[0]."'";
      $str .= "\n--\n\n";
      $str .= "ALTER TABLE ONLY ".$row[0] . " ADD CONSTRAINT " . $row[1] . 
  " " . $row[2] . ";";
    }       
    fwrite($back,$str);
    fclose($back);
    dl_file("$dbname.sql");
    break;
  }
  
  ?>
   
  <html>
  <head>
  </head>
  <body>
  <form id="dataForm" name="dataForm" method="post" 
  enctype="multipart/form-data" action="">
      <input type="file" name="path" id="path" style="width:300px"/>
      <input type="submit" value="Import" name="actionButton" 
  id="actionButton" >
      <input type="submit" value="Export" name="actionButton" 
  id="actionButton" >
  </form>
  </body>
  </html>
  

  
  
  Fuente: https://www.postgresql.org/message-id/43B43971.6060407@ydreams.com   Votes: 0 - Link answer