fetching Remote table data and storing into local dump file in pgSQL?

Question

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

Answer 1

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();
        }
    }
}

Answer 2

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.