0

This question already has an answer here:

I want to copy one table's content from a remote machine to local. I have tried with psql Command \copy. It's working fine as "copy" is not working due to copying from remote.

But is there any way I can do the same without the command console, and execute a simple query through c# and do it programatically? I am using Postgres 9.4

marked as duplicate by Paul White Dec 16 '16 at 2:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Did you try with pg_dump, pg_restore on pgAdmin ? – Luan Huynh Dec 12 '16 at 10:08
  • 2
    Why do you want to do this programmatically? Is it a recurring task? – dezso Dec 12 '16 at 10:25
  • @LuanHuynh I think we cant pass column names in pg_dump. By default it's taking *. – Shashi Dec 14 '16 at 13:38
  • @Shashi: well, you can clone a table with your columns create table t1 as select col1, col2 ... from table_a, then dump/restore t1 . (dump + zip) – Luan Huynh Dec 14 '16 at 14:41
  • What the matter with dump all columns or not ? After restore you can select columns you need. – Luan Huynh Dec 14 '16 at 15:02
0

Using C#, you could have a relatively simple program. For instance, if you want to write to a CSV file, you could use the following code:

using System.IO;  /* We want to Write to the FileSystem */
using Npgsql;     /* Connection to PostgreSQL database */
using CsvHelper;  /* CSV writer - https://joshclose.github.io/CsvHelper/ */

namespace ConnectToDB
{
    class Program
    {
        const string connectionString = 
            "Server=localhost;" + 
            "Database=joanolo;" + 
            "User Id=user;" + 
            "Password=password;";
        const string fileName = "Output.csv";
        const string SQLCommand = "SELECT * FROM t";

        static void Main(string[] args)
        {
            /* Connect to the database */
            NpgsqlConnection DB = new NpgsqlConnection(connectionString);
            DB.Open();

            /* Prepare to read data */
            NpgsqlCommand Cmd = new NpgsqlCommand(SQLCommand, DB);
            NpgsqlDataReader dataReader = Cmd.ExecuteReader();

            /* Create CSV writer */
            StreamWriter outputFile = new StreamWriter("Output.csv") ;
            var csv = new CsvWriter(outputFile);
            csv.Configuration.Delimiter = ",";
            csv.Configuration.Quote = '"';
            csv.Configuration.QuoteAllFields = true;

            /* Write Data Header */
            for (var i = 0; i < dataReader.FieldCount; i++)
            {
                csv.WriteField(dataReader.GetName(i));
            }
            csv.NextRecord();

            /* Read data from DB and write it to CSV */
            while (dataReader.Read())
            {
                for (var i = 0; i < dataReader.FieldCount; i++)
                {
                    var value = dataReader[i].ToString();
                    csv.WriteField(value);
                }
                csv.NextRecord();
            }

            /* Close everything */
            outputFile.Close();
            DB.Close();
        }
    }
}
  • I have tried as u mentioned above, Due to table having millions of records it's taking a lot of time to finish the query. – Shashi Dec 14 '16 at 10:31
  • and boolean column in table value "t" stores as "true" in .csv file. – Shashi Dec 14 '16 at 10:35
  • If you need a boolean to be written in any other way, you can change the way you make your query (SQLCommand = ) and make it do the conversion to whatever is convenient for you. Something like SELECT (CASE WHEN boo THEN 1 ELSE 0 END) AS boolean_written_as_0_or_1 FROM t. If you need really big speed because your tables are big, explore NpgsqlBinaryExporter and BeginBinaryExport and adapt it to your needs. – joanolo Dec 14 '16 at 21:54
  • You may as well check Copy. – joanolo Dec 14 '16 at 21:58
0

Connect to the remote machine with psql, not with ssh or the like, and then run.

psql -d database -h myHost -p portNum

\COPY ...

That's \COPY, not COPY. It will work.

You can create an ssh tunnel, but if you're connecting to the remote with ssh and running psql locally that'll just get your dump on the remote machine. You'll still have to transfer it with rsync/scp, etc.

Not the answer you're looking for? Browse other questions tagged or ask your own question.