Azure -B环境下Linux主机间连接并获取PostgreSQL数据脚本求助

Hey there! Since you're new to Linux and working in Azure, let me walk you through both the manual way to connect and query PostgreSQL, plus how to wrap this into a reusable script. I'll keep things straightforward so you can follow along easily.

第一步：先打通源主机到目标主机的SSH连接（Azure专属注意点） #

Before doing anything with PostgreSQL, you need to be able to connect to the target host from your source machine. In Azure, there are a couple quick checks first:

• Make sure the Network Security Group (NSG) attached to your target VM allows inbound SSH traffic (port 22) from your source VM's IP address.
• If you're using Azure VMs, confirm both hosts are in the same virtual network (or peered networks) so they can communicate.

手动SSH连接命令 #

Once the network is set up, run this on your source host to connect to the target:

ssh your_target_username@target_host_public_ip_or_private_ip

• If you use SSH keys instead of passwords (highly recommended for security), specify your private key path:

  ssh -i /path/to/your/private_key.pem your_target_username@target_host_ip
  

• First time connecting? You'll get a fingerprint prompt—just type yes to confirm and proceed.

第二步：查询目标主机上的PostgreSQL数据（两种方式） #

Once you're connected to the target host (or even directly from the source, if you configure remote PostgreSQL access), here's how to pull data:

方式1：SSH到目标主机后本地查询PostgreSQL #

After SSHing into the target, use the psql client to connect to your PostgreSQL instance:

# 连接到指定数据库和用户
psql -U your_postgres_username -d your_database_name

Once you're in the psql shell, run your query like:

SELECT * FROM your_table_name LIMIT 10; -- 示例查询，按需修改

To export results to a file (so you can copy it back to the source), run this directly from the target's bash shell (no need to enter psql):

psql -U your_postgres_username -d your_database_name -c "SELECT * FROM your_table_name;" > pg_exported_data.csv

方式2：源主机直接远程连接PostgreSQL（更高效） #

If you don't want to SSH into the target first, you can connect directly from the source to the target's PostgreSQL instance. Here's what you need to set up:

1. On the target host:
   • Edit the PostgreSQL config file postgresql.conf (usually in /etc/postgresql/<version>/main/) to allow remote connections:

     listen_addresses = '*'
     

   • Edit pg_hba.conf (same directory) to add your source host's IP to the allowed connections:

     host    your_database_name    your_postgres_username    source_host_ip/32    scram-sha-256
     

   • Restart PostgreSQL to apply changes:

     sudo systemctl restart postgresql
     

2. In Azure:
   • Update the target VM's NSG to allow inbound traffic on PostgreSQL's default port (5432) from the source host's IP.
3. On the source host:
   • Run this command to connect directly and run your query:

     psql -U your_postgres_username -h target_host_ip -d your_database_name -c "SELECT * FROM your_table_name;" > local_export.csv
     

第三步：编写自动化脚本实现整个流程 #

To save time, you can turn the above steps into a bash script. Here's a sample that uses SSH to run the query on the target and pulls the results back to the source:

#!/bin/bash

# -------------------------- 配置信息 --------------------------
TARGET_USER="your_target_linux_username"
TARGET_IP="xxx.xxx.xxx.xxx" # 目标主机的IP
PG_USER="your_postgres_username"
PG_DB="your_database_name"
QUERY="SELECT * FROM your_table_name;" # 你的查询语句
LOCAL_OUTPUT="pg_data_from_target.csv" # 本地保存的文件名
TEMP_FILE="/tmp/temp_pg_export.csv" # 目标主机上的临时文件路径
# -------------------------------------------------------------

# 1. SSH到目标主机执行查询并导出到临时文件
echo "Running query on target host..."
ssh $TARGET_USER@$TARGET_IP "psql -U $PG_USER -d $PG_DB -c '$QUERY' > $TEMP_FILE"

# 2. 将临时文件从目标主机复制到源主机
echo "Copying data to local host..."
scp $TARGET_USER@$TARGET_IP:$TEMP_FILE ./$LOCAL_OUTPUT

# 3. 清理目标主机上的临时文件
echo "Cleaning up temp files on target..."
ssh $TARGET_USER@$TARGET_IP "rm $TEMP_FILE"

echo "Done! Data saved to ./$LOCAL_OUTPUT"

脚本使用注意事项 #

• Give the script execution permissions:

  chmod +x fetch_pg_data.sh
  

• To avoid entering your SSH password every time, set up passwordless SSH authentication:

  ssh-copy-id $TARGET_USER@$TARGET_IP
  

• Double-check all config values (usernames, IPs, query) match your environment before running.

内容的提问来源于stack exchange，提问作者Bobby